实体框架中的复杂日期时间查询

Question

我想问一下如何在实体框架中编写复杂的DateTime查询，如下所示：

我在服务中有以下代码：

Func<DateTime, DateTime> calculate24HoursLater = (date) =>
{
    if (date.DayOfWeek == DayOfWeek.Friday)
        return date.AddDays(3);
    if (date.DayOfWeek == DayOfWeek.Saturday)
        return date.AddDays(3).Date;
    if (date.DayOfWeek == DayOfWeek.Sunday)
        return date.AddDays(2).Date;
    return date.AddDays(1);
};
var unactionedEnquiries = 
    dataContext.ContactedBrokerEnquiries
        .Include("ContactedBrokers")
        .Where(
            x => x.ContactedBrokers.All(c => c.Status == (byte)LeadStatus.Rejected) || 
                x.ContactedBrokers.Any(c => c.Status == (byte)LeadStatus.New && calculate24HoursLater(c.CreatedDate) < DateTime.Now)
        ).OrderByDescending(c => c.CreatedDate);

结果unactionedEnquiries，我估计应该是智商的。这意味着SQL服务器直到我的下一条语句才会执行

但是，我得到例外calculate24HoursLater(c.CreatedDate) < DateTime.Now)此语句不能转换为 SQL 语句。我知道原因，但我不知道如何在Entity Framework查询中写下该rule

重要提示：我不喜欢将所有数据推送到 RAM 中，然后使用该条件进行过滤。理想情况下，它应该在SQL-Server

你能告诉我如何在SQL-EF语句中编写它们吗？

Answer 1

您可能希望查看可能使用 SQLFunctions 方法，这些方法可用于在 LINQ 查询中执行日期操作

示例（未经测试） 尝试将 Func 定义替换为以下内容：

Func<DateTime, DateTime> calculate24HoursLater = (date) =>
{
    if (date.DayOfWeek == DayOfWeek.Friday)
        return SqlFunctions.DateAdd("day", 3, date).Value;
    if (date.DayOfWeek == DayOfWeek.Saturday)
        return SqlFunctions.DateAdd("day", 3, date).Value;
    if (date.DayOfWeek == DayOfWeek.Sunday)
        return SqlFunctions.DateAdd("day", 2, date).Value;
    return SqlFunctions.DateAdd("day", 1, date).Value;
};

Answer 2

正如我在这里看到@andrew的天才答案，你可以做到：

var calcDate = DateTime.Now.AddHours(-24);
var unactionedEnquiries = 
    dataContext.ContactedBrokerEnquiries
        .Include("ContactedBrokers")
        .Where(
            x => x.ContactedBrokers.All(c => c.Status == (byte)LeadStatus.Rejected) || 
                x.ContactedBrokers.Any(c => c.Status == (byte)LeadStatus.New && c.CreatedDate < calcDate)
        ).OrderByDescending(c => c.CreatedDate);