Have you ever had an issue with order of items stored in your Entity Framework data when querying by datetime? The other day I was querying some data by the datetime the items were created. What I found was the items did not return back in the order in which they were created. I was really confused.

Consider the code below. What would you expect to be printed out?

var expected = new Item { CreatedOn = DateTime.Now };

using (var context = new DataContext())
{
    context.Items.Add(expected);
    context.SaveChanges();
}

using (var context = new DataContext())
{
    var actual = context.Items.First(x => x.Id == expected.Id);
    Console.WriteLine(expected.CreatedOn);
    Console.WriteLine(actual.CreatedOn);
    Console.WriteLine("Result: " + (expected.CreatedOn == actual.CreatedOn));
}

By just reading the code you would expect the result to be "True" but you get the following result.

7/26/2015 8:22:31 AM
7/26/2015 8:22:31 AM
Result: False
:blue The problem is precision.

The problem comes from the fact that the default time used for DateTime when mapping your item is the SQL data type of [datetime]. The SQL [datetime] data type has a resolution of 3.33 ms and the C# [DateTime] data type has a resolution of 100 ns. This is a huge difference.

This is why in my scenario I was reading back items saved at the same time frame were coming back in the order in which SQL decided because they are all equal in time due to the loss in precision.

Let's print out the "ticks" and see the loss difference.

7/26/2015 8:22:31 AM : 635734957519050899
7/26/2015 8:22:31 AM : 635734957519070000
Result: False
:blue How do we fix it?

We can fix it by using a mapping file to tell EntityFramework to use the SQL [datetime2] type instead. The SQL [datetime2] data type has the same resolution as the C# [DateTime] data type of 100 ns.

We'll add a new CreatedOn2 property and map it to the SQL [datetime2] type.

modelBuilder.Entity<Item>().Property(x => x.CreatedOn2).HasColumnType("datetime2");

Now when we run the same test including the new property the new property results is true as expected.

7/26/2015 8:37:46 AM : 635734966664174337
7/26/2015 8:37:46 AM : 635734966664170000
Result: False

7/26/2015 8:37:46 AM : 635734966664174337
7/26/2015 8:37:46 AM : 635734966664174337
Result2: True
:blue What did we learn?

Know your data types and how your Entity Framework context maps your models to your data. It's crucial to know how your data is being stored and that you are not losing data by simply not knowing. Get the full code here at my GitHub Project

Continue learning and Become EPIC!