'How can I remove the time part from a nullable datetime in where condition of Linq

I have a trouble with my existing Linq statement to apply the nullable datetime model value in where condition . When I apply the datetime with shortdatestring(), the following error is shown:

.Where(ti => ti.Outer.EmpCode == __empcode_0 && ti.Outer.ClockDateFormatted == __ToShortDateString_1)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

Here is the Code

Attendance model

public string Code { get; set; }
public DateTime? ClockedDate { get; set; }

public ICollection<Attendance> GetClockList(string code, DateTime datefrom)
{
    ICollection<Attendance> data = new List<Attendance>();
    data = (from log in ctx.Attendance
            join emp in ctx.Employee on log.EmpCode equals emp.EmployeeID.ToString()
            where log.EmpCode == empcode
            && log.ClockedDate.Value.ToShortDateString() == datefrom.ToShortDateString() // Here I have to give the ClockedDate without time 
            select new Attendance 
            {
              
                EmployeeName = emp.EmployeeName,
                ClockedDate = log.ClockedDate,

            }
            ).ToList();

    return data;
}

But the error is showed while giving the datetime with shortdateString() in where condition

c++sharpentity-frameworklinq.net-coreentity-framework-core


Solution 1:[1]

if you use ms sql you can try this

.....

  where log.EmpCode == empcode
         && EF.Functions.DateDiffDay(log.ClockedDate, datefrom)==0

Solution 2:[2]

You can try

 && log.ClockedDate?.Date == datefrom.Date

but if that doesn't work you will probably need EF Functions.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Serge
Solution 2 Henk Holterman

Related Questions