How to Use Dates in Where Clause in EF Core?

Question

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).

Why not to simply use this?: `Where(x => x.CreatedDate.Date == now)` It should execute on server — tgralex, Jan 06 '20 at 18:05
No. On server. Just checked that `WHERE(CreatedDate.Date == now)` translates to `WHERE ((CONVERT(date, [x].[CreatedDate]) = '2019-01-01T00:00:00.000')` — tgralex, Jan 07 '20 at 14:27
weird, let me try again as in my logs it said it would be converted locally. — chobo2, Jan 08 '20 at 18:34

Igor · Accepted Answer · 2020-01-07T14:48:32.410

3

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.

edited Jan 07 '20 at 14:48

answered Jan 06 '20 at 18:14

Igor

60,821
10
100
175

Yeah, .Date does not seem supported, how do I check if they are valid without writing the code first and waiting to see the error log. – chobo2 Jan 06 '20 at 18:27
@chobo2 - You can check https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/canonical-functions – Igor Jan 06 '20 at 18:30
Just checked regarding DateTime.Date. Verified that `WHERE(CreatedDate.Date == now)` translates to `WHERE ((CONVERT(date, [x].[CreatedDate]) = '2019-01-01T00:00:00.000')` – tgralex Jan 07 '20 at 14:27
@tgralex - good to know. Even though that code might be easier to read it will result in a non-sargable query though as the query optimizer can't take advantage of any indexes that exist on `CreatedDate` (because of `CONVERT`). – Igor Jan 07 '20 at 14:30
1

Just checked on a table which has 200K records with index by that datetime field. Estimated execution shows no difference whether with or without convert. SQL Servers are quite smart nova days... – tgralex Jan 07 '20 at 14:41
1

@tgralex - so it is. – Igor Jan 07 '20 at 14:48

score 1 · Answer 2 · answered Jan 07 '20 at 14:31

Use

var now = DateTime.Now.Date
...WHERE(CreatedDate.Date == now)

I just checked that above translates to the following SQL query:

WHERE ((CONVERT(date, [x].[CreatedDate]) = '2019-01-07T00:00:00.000')

I used this (link) method to see what LINQ translates to

How to Use Dates in Where Clause in EF Core?

2 Answers2