Query a date column in EF Core

Question

I need to query an existing database table that has a column of sql type datetime

EF Core maps DateTime properties to [datetime2](7) . That did not matter in EF Core 3.1.

Queries like db.Blogs.Where( b => b.StartDate > new DateTime(2020,1,1)) were still translated to something like WHERE StartDate > '2020-01-01T00:00:00.00'

I updated to EF to Core 6.0 and such a query now leads to something like WHERE [StartDate] > '2020-01-01T00:00:00.0000000' which works for a column of type [datetime2](7) but not of type datetime

Since the table is used by EF (Non core) and EF Core the column should stay datetime but I need to be able to filter it. Is there a way to do that?

Answer 1

If you don't need time info in query you can just drop it. You can use DbFunctions.TruncateTime method for .net version and EntityFunctions.TruncateTime method for core version. Just truncate time info from column with time info.

db.Blogs.Where( b => b.StartDate > DbFunctions.TruncateTime(yourdateTime))

Should work.

Since the question is about EF Core it should be

db.Blogs.Where( b => b.StartDate.Date  > EntityFunctions.TruncateTime(yourdateTime))