Best way to compare date in Entity Framework

Question

I am using date in where clause in Entity Framework and getting following error:

It is due to the below code:

var entity = dbContext.MyTable
                      .Where(w => w.PId = 3 && w.CreatedOn.Date == mydate.Date)
                      .First();

If I convert data into list using .ToList(), then compare to date, it will work fine but if I do so then it will pull data first into code then filter it out. Please help me out with this issue. Currently I am using a stored procedure to resolve the issue but really want this to work.

Answer 1

You can use DbFunctions.TruncateTime canonical function like this

var entity = dbContext.MyTable
    .Where(w => w.PId == 3 && DbFunctions.TruncateTime(w.CreatedOn) == mydate.Date)
    .First();