Search code examples
c#sqldatetimeef-core-2.2

How to Use Dates in Where Clause in EF Core?

I need to filter my queries by dates but I don't care in this case about time portion of it that is stored in SQL Database.

I first tried to something like

var now = DateTime.Now.Date;
Where(x => x.CreatedDate.Date.Compare(now) == 0)

but this seems to all get locally checked making the query slow. How can I do this without making it do the check locally?

I am pretty much trying to just find all results that would say have happened today(2020-01-06).

Solution

  • There are a limited number of methods you can use on translatable types when constructing your Lambda / Linq expressions. This is because each method would need additional code so that it could be translated into a sql store expression. It means that you must check that any methods you want to use and expect to be translated into a sql store expression are supported.

    In this case the DateTime.Compare is not supported.

    The easiest thing to do here is a simple range comparison because the time is included in your persisted value.

    var start = DateTime.Now.Date;
var end = start.AddDays(1);

Where(x => x.CreatedDate >= start && x.CreatedDate < end)

    This will result in a sargable query.