能否在Entity Framework的LINQ表达式中调用自定义函数？

问题原因 #

你遇到的异常是因为Entity Framework无法将自定义C#方法GetLastDayOfQuarter转换为对应的SQL语句。LINQ to Entities要求查询中的所有逻辑都能被EF解析并转换为数据库可执行的SQL，自定义方法不在EF的可解析范围内。

可行解决方案 #

1. 将计算逻辑直接内联到LINQ查询中

把GetLastDayOfQuarter的计算逻辑拆解后直接写到where子句里，让EF能解析为SQL：

var taskDto = await (
    from task in dbContext.TbTasks
    join tu in dbContext.TbTaskUsers on task.Id equals tu.Taskid
    join tt in dbContext.TbTaskTypes on task.Tasktypeid equals tt.Id
    where tu.Customercode == customerCode && task.Startdate < DateTime.Now && 
        ((task.Enddate > DateTime.Now && tu.Status == 0) || 
         (task.Tasktypeid == 4 && task.Giftcount >= 50 && 
          DateTime.Now < new DateTime(task.Enddate.Year, 
                                      task.Enddate.Month + 3 - task.Enddate.Month % 3, 
                                      DateTime.DaysInMonth(task.Enddate.Year, task.Enddate.Month + 3 - task.Enddate.Month % 3))))
    select new CustomerTaskDto()
    {
        ResponseTime = tu.Responsetime,
        TaskPeriod = $"{task.Createdate} - {task.Enddate}",
        TaskType = tt.Id == 4 && task.Giftcount >= 50 ? "Süper Yap Kazan" : tt.Name,
        TaskTypeId = tt.Id,
        EndDate = task.Enddate,
        TaskId = task.Id
    }).ToListAsync();

return taskDto;

2. 使用表达式树封装可复用逻辑

如果需要多次复用季度最后一天的计算逻辑，可以将其封装为表达式树，EF能直接解析表达式树并转换为SQL：

// 定义可被EF解析的表达式树
private static Expression<Func<DateTime, DateTime>> GetLastDayOfQuarterExpr = date =>
    new DateTime(date.Year, date.Month + 3 - date.Month % 3, DateTime.DaysInMonth(date.Year, date.Month + 3 - date.Month % 3));

// 在查询中调用
var taskDto = await (
    from task in dbContext.TbTasks
    join tu in dbContext.TbTaskUsers on task.Id equals tu.Taskid
    join tt in dbContext.TbTaskTypes on task.Tasktypeid equals tt.Id
    let quarterEnd = GetLastDayOfQuarterExpr.Compile()(task.Enddate)
    where tu.Customercode == customerCode && task.Startdate < DateTime.Now && 
        ((task.Enddate > DateTime.Now && tu.Status == 0) || 
         (task.Tasktypeid == 4 && task.Giftcount >= 50 && DateTime.Now < quarterEnd))
    select new CustomerTaskDto()
    {
        ResponseTime = tu.Responsetime,
        TaskPeriod = $"{task.Createdate} - {task.Enddate}",
        TaskType = tt.Id == 4 && task.Giftcount >= 50 ? "Süper Yap Kazan" : tt.Name,
        TaskTypeId = tt.Id,
        EndDate = task.Enddate,
        TaskId = task.Id
    }).ToListAsync();

3. 映射数据库内置函数（以SQL Server为例）

如果使用SQL Server，可以利用数据库原生日期函数计算季度最后一天，通过EF的函数映射实现：

首先在DbContext中定义映射到SQL函数的方法：

[DbFunction("GetLastDayOfQuarter", "dbo")]
public static DateTime GetLastDayOfQuarter(DateTime date)
{
    throw new NotImplementedException("该方法仅用于EF SQL转换，不会在C#中执行");
}

然后在SQL Server中创建对应的标量函数：

CREATE FUNCTION dbo.GetLastDayOfQuarter(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @date) + 1, 0))
END

之后就可以在LINQ查询中直接调用该方法，EF会自动转换为调用数据库函数：

var taskDto = await (
    from task in dbContext.TbTasks
    join tu in dbContext.TbTaskUsers on task.Id equals tu.Taskid
    join tt in dbContext.TbTaskTypes on task.Tasktypeid equals tt.Id
    where tu.Customercode == customerCode && task.Startdate < DateTime.Now && 
        ((task.Enddate > DateTime.Now && tu.Status == 0) || 
         (task.Tasktypeid == 4 && task.Giftcount >= 50 && DateTime.Now < GetLastDayOfQuarter(task.Enddate)))
    select new CustomerTaskDto()
    {
        ResponseTime = tu.Responsetime,
        TaskPeriod = $"{task.Createdate} - {task.Enddate}",
        TaskType = tt.Id == 4 && task.Giftcount >= 50 ? "Süper Yap Kazan" : tt.Name,
        TaskTypeId = tt.Id,
        EndDate = task.Enddate,
        TaskId = task.Id
    }).ToListAsync();

内容的提问来源于stack exchange，提问作者ferhat tumer