Date comparison with Entity Framework

5 min readNov 30, 2019

Entity Framework allows you to search for data by using Linq (language integrated query). This means that an Sql statement will be generated based on the said query which has been written in code.

Dates can be funny

Any of us comparing data according to dates or searching for data in between two dates might not be so concerned about the specific time, The data type used to store dates in .NET is however DateTime, which also just happens to store time too as it’s name suggests.

sample date: 01/08/2019 12:08:33.790

The above sample date shows how Microsoft SQL server stores dates accurate to the millisecond which is how the typical DateTime value works. This level of accuracy can be very problematic for the person who does not really care about what time is attached to the date. Usually, dates passed into a query to look for the record of the sample date shown above will look like: 01/08/2019 00:00:00.000 which means that a query to get a record based on that date without the time will most likely not return any data.

Let’s show some code

Create a new .NET 4.8 console application

2. Add a DbContext

public class DateQueryDbContext : DbContext
{
        
   public DateQueryDbContext() : base(nameOrConnectionString: "Data     Source=localhost;Initial Catalog=DateQuery;Integrated Security=true;"){ }
 
        
       
}

3. Add the type User for our test purposes

public class User
{
   public int Id { get; set; }
   public string Name { get; set; }
   public DateTime AddedDate { get; set; }
}

4. Add the public DbSet<User> Users { get; set; } to the DbContext such that it looks as follows:

DbContext after adding the ‘Users’ DbSet

5. Enable Migrations and Update database

To enable migrations in a code first project, we run enable-migrations in the package-manager console available at: Tools -> Library Package Manager -> Package Manager Console

then initialise a new migration by running add-migration Initial where ‘Initial’ is the name of the migration. Finally run update-database to execute the generated migrations against the database.

6. Add sample data at the gist below:
https://gist.github.com/duhowise/96c53a216067a8160689c100add7510b

We then run a raw query to search for the sample date shown in the example above.

Using the specific date and time:

SELECT * FROM Users u where u.AddedDate =’2019–08–01 12:08:33.790'

result: [ {“Id”:”35",”Name”:”Aiko2002",”AddedDate”:”01/08/2019 12:08:33"}]

Using the date without any time:

SELECT * FROM Users u where u.AddedDate =’2019–08–01 00:00:00.000'

result: []

We see that the query returns no result based on the fact that both times are not the same.

How to handle this with SQL?

Sql Server however has a type specific to only dates (without the time) called DATE, this allows us to cast the source DateTime into a DATE before comparison and providing us with a way to query the date fields without regard for their attached times as follows:

SELECT * FROM Users u WHERE cast (u.AddedDate AS DATE) =’2019–08–01 00:00:00.000'

result: [ {“Id”:”35",”Name”:”Aiko2002",”AddedDate”:”01/08/2019 12:08:33"}]

How about Entity Framework?

Using specific DateTime :

result:

Using Date without any time

This brings back an empty result as expected

How to handle this with entity framework?

Quite recently, I began to manage a fairly old code base and saw a number of ways that this problem was being “managed” including expanding the range of BETWEEN queries for items between two date ranges by calling AddDays(1) before passing the parameters to be executed. Naturally I did not like the workaround and sought to correct it which lead me to realise the “DateTime search behaviour”.

After a few searches I found DbFunctions in System.Data.Entity

DbFunctions has a number of static methods, among which is TruncateTime(Nullable<DateTime>)which allows you to: “ invoke the canonical TruncateTime EDM function to return the given date with the time portion cleared”.

How does Truncate Time work?

Using the `TruncateTime` function to compare dates

the above is almost the same query as the above without specific dates except for the use of TuncateTime , The result:

So what does TruncateTime actually do?

Let’s take a look by adding a line of code context.Database.Log = Console.WriteLine; before the query to enable us view the generated SQL.

Without TruncateTime:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[AddedDate] AS [AddedDate]
    FROM [dbo].[Users] AS [Extent1]
    WHERE [Extent1].[AddedDate] = @p__linq__0-- p__linq__0: '01/08/2019 00:00:00' (Type = DateTime2, IsNullable = false)

With TruncateTime:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[AddedDate] AS [AddedDate]
    FROM [dbo].[Users] AS [Extent1]
    WHERE (cast(cast([Extent1].[AddedDate] as date) as datetime2)) = @p__linq__0-- p__linq__0: '01/08/2019 00:00:00' (Type = DateTime2, IsNullable = false)

Final Notes

It is clearly visible that querying dates without using the appropriate types can produce wrong results. Casting to the DATE type for raw queries and using TruncateTime can really help simplify your ling queries. You can find the source files at: https://github.com/duhowise/EntityFrameworkDateQuery