Is there any chance to call Expression in Linq (Entity Framework)

Question

I am trying to use filter in the linq expression in Entity Framework. My function is:

private static DateTime GetLastDayOfQuarter(DateTime dateTime)
{
    return new DateTime(dateTime.Year, dateTime.Month + 3 - dateTime.Month % 3, DateTime.DaysInMonth(dateTime.Year, dateTime.Month + 3 - dateTime.Month % 3));
}

Query:

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();

return taskDto;

I tried to call function, expression or etc on the where clause. Nothing solved the exception. Is there any possibility to call it?

It is EF 6? No, it is not possible, EF cannot look into compiled method body and understand which SQL to generate, but there are a lot of extensions which can help here. — Svyatoslav Danyliv, Jul 13 '23 at 11:29
A lot of [them](https://stackoverflow.com/questions/66378438/can-i-reuse-code-for-selecting-a-custom-dto-object-for-a-child-property-with-ef/66386142#66386142), LINQKit will work with EF6. But anyway, ensure that inlined function body in your filer works. I have bad feelings. — Svyatoslav Danyliv, Jul 13 '23 at 11:37
This one is very powerful : https://github.com/gaiazov/QueryInterceptor — Aluan Haddad, Jul 13 '23 at 15:22
When you're working with continuous ranges such as datetimes, it's often a better idea to use an *exclusive* endpoint rather than an inclusive one, and to thus use `<` comparisons rather than `<=`. Unless it's your intention that your code acts differently *during* the last day of each quarter, as your current code does (due to you not accounting for the *time* part of your datetime values). — Damien_The_Unbeliever, Jul 18 '23 at 12:46

score 1 · Answer 1 · answered Jul 18 '23 at 09:51

Entity Framework will have to translate your code into TSQL code before querying. Thus, it can't translate complex expressions. The only way you can achieve that is by fetching rough data into a List and then apply whatever the filtering conditions you like with LINQ to Objects.

Is there any chance to call Expression in Linq (Entity Framework)

1 Answers1