SqlServer & EF7: Using datetime vs datetime2 date type

Mark Pelf

5.00/5 (7 votes)

Jun 9, 2023

CPOL

3 min read

7209

Showing problems with limited accuracy of SqlServer data type “datetime” in .NET7 EF7 environment

Download source

1 Introduction

It all started as a practical problem. I was working on some legacy SqlServer database, writing my .NET/C# code when I noticed WEIRED behavior when working with Timestamps. That pointed me to explore issues more in-depth and here is this article. Originally, I saw issues in .NET 4.8 Framework/EF6 environment, but examples in this article are .NET7/EF7 Core environment and issues are still here.

The main problem is the limited accuracy of SqlServer data type “datetime” and rounding that is happening on the database side. A newer version of SqlServer data type “datetime2” has better accuracy and no rounding problem.

Usage of .NET Entity Framework just contributes to confusion during work with SqlServer data type “datetime”, since rounding of data happens when background SQL queries are executed in real but not when LINQ is executed in memory. Also, EF will try to fill your queries from EF Cache, so it might appear that you have better accuracy than you really have since data in EF Cache has better accuracy compared to real data in the database.

2 SqlServer data type “datetime” vs “datatime2”

Based on [1] and [2], here is a small table that outlines the differences between the SqlServer data types “datetime” and “datatime2” relevant to this article.

3 Sample database

We will try to show problems with some sample C# code. For that, we need a small database that contains data types “datetime” and “datetime2”. Here is our small database, with the database table “People” which contains both data types.

4 C# Example 1

To demo problems, we created C# .NET7 test application and used Entity Framework 7, a Database-first approach (see [3]) to access the database.

Here is our program.

Here is the code of our Example1.

using ExampleE1;
using ExampleE1.TestE1DB;

Console.WriteLine("Hello from ExampleE1");

//creating timestamps===============
Console.WriteLine("\nCreating timestamps===============");
// 2023-05-07-11:12:13.1234567
DateTime dt1 = new DateTime(2023, 5, 7, 11, 12, 13, 123, 456);
dt1 = dt1.AddTicks(7);
Console.WriteLine("dt1: " + dt1.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

// 2023-05-07-11:12:13.1244567
DateTime dt2 = new DateTime(2023, 5, 7, 11, 12, 13, 124, 456);
dt2 = dt2.AddTicks(7);
Console.WriteLine("dt2: " + dt2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

// 2023-05-07-11:12:13.1224567
DateTime dt3 = new DateTime(2023, 5, 7, 11, 12, 13, 122, 456);
dt3 = dt3.AddTicks(7);
Console.WriteLine("dt3: " + dt3.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //insert into database==========================
    Console.WriteLine("\nInsert into database===============");
    People p1 = new People();
    p1.ID = Guid.NewGuid();
    p1.Name = "Mark";
    p1.TsDatatime = dt1;
    p1.TsDatatime2 = dt1;
    ctx.People.Add(p1);

    People p2 = new People();
    p2.ID = Guid.NewGuid();
    p2.Name = "John";
    p2.TsDatatime = dt2;
    p2.TsDatatime2 = dt2;
    ctx.People.Add(p2);

    People p3 = new People();
    p3.ID = Guid.NewGuid();
    p3.Name = "Rafa";
    p3.TsDatatime = dt3;
    p3.TsDatatime2 = dt3;
    ctx.People.Add(p3);

    ctx.SaveChanges();

    //read from database 1==========================
    Console.WriteLine("\nRead from database 1 - Getting values from EF cache===============");

    foreach (People p in ctx.People)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //read from database 2==========================
    Console.WriteLine("\nRead from database 2 - Real values from database, because it is new EF context===============");

    foreach (People p in ctx.People)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

And here is the execution result:

Hello from ExampleE1

Creating timestamps===============
dt1: 2023-05-07-11:12:13.1234567
dt2: 2023-05-07-11:12:13.1244567
dt3: 2023-05-07-11:12:13.1224567

Insert into database===============

Read from database 1 - Getting values from EF cache===============
Name: Mark TsDatatime: 2023-05-07-11:12:13.1234567 TsDatatime2: 2023-05-07-11:12:13.1234567
Name: Rafa TsDatatime: 2023-05-07-11:12:13.1224567 TsDatatime2: 2023-05-07-11:12:13.1224567
Name: John TsDatatime: 2023-05-07-11:12:13.1244567 TsDatatime2: 2023-05-07-11:12:13.1244567

Read from database 2 - Real values from database, because it is new EF context===============
Name: Mark TsDatatime: 2023-05-07-11:12:13.1230000 TsDatatime2: 2023-05-07-11:12:13.1234567
Name: Rafa TsDatatime: 2023-05-07-11:12:13.1230000 TsDatatime2: 2023-05-07-11:12:13.1224567
Name: John TsDatatime: 2023-05-07-11:12:13.1230000 TsDatatime2: 2023-05-07-11:12:13.1244567

And database state

The loss of accuracy and rounding problems are obvious now from the database table state.
The funny thing is that EF will in the first database query try to fill data from EF Cache and it creates the illusion that we have better accuracy than we really have.

5 C# Example 2

The second demo application will show the rounding problem that happens in database queries but not in LINQ queries in memory.

Here is our program.

Here is the code of our Example2.

using ExampleE2;
using ExampleE2.TestE1DB;

Console.WriteLine("Hello from ExampleE2");

//creating timestamps===============
Console.WriteLine("\nCreating timestamps===============");
//since datetime is rounded to a precision of a milisecond
//we will create timestams of same precision
// 2023-05-07-11:12:13.126
DateTime dt1 = new DateTime(2023, 5, 7, 11, 12, 13, 126);
Console.WriteLine("dt1: " + dt1.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //insert into database==========================
    Console.WriteLine("\nInsert into database===============");
    People p1 = new People();
    p1.ID = Guid.NewGuid();
    p1.Name = "Pelf";
    p1.TsDatatime = dt1;
    p1.TsDatatime2 = dt1;
    ctx.People.Add(p1);

    ctx.SaveChanges();

    //read from database 1==========================
    Console.WriteLine("\nRead from database 1 - Getting values from EF cache===============");

    List<People>? listPeople = ctx.People.Where(p=> p.TsDatatime == dt1).ToList();

    People? p1Found = listPeople.Where(p => p.TsDatatime == dt1).FirstOrDefault();

    if (p1Found != null)
    {
        Console.WriteLine("Found! Timestamps match!");
        Console.WriteLine("Name: " + p1Found.Name + " TsDatatime: " + p1Found.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p1Found.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
    else
    {
        Console.WriteLine("Not Found!");
    }
}


using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //read from database 2==========================
    Console.WriteLine("\nRead from database 2 - Real values from database, because it is new EF context===============");

    //this is real query execution, so rounding of dt1 is happening on the database side
    List<People>? listPeople = ctx.People.Where(p => p.TsDatatime == dt1).ToList();

    //this is LINQ query in memory, so there is no rounding of dt1
    People? p1Found = listPeople.Where(p => p.TsDatatime == dt1).FirstOrDefault();

    if (p1Found != null)
    {
        Console.WriteLine("Found! Timestamps match!");
        Console.WriteLine("Name: " + p1Found.Name + " TsDatatime: " + p1Found.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p1Found.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
    else
    {
        Console.WriteLine("Not Found! Problem with rounding of timestamps caused mismatch.");
    }

    Console.WriteLine("Show list so to see what is there===============");
    foreach (People p in listPeople)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

And here is the execution result:

Hello from ExampleE2

Creating timestamps===============
dt1: 2023-05-07-11:12:13.1260000

Insert into database===============

Read from database 1 - Getting values from EF cache===============
Found! Timestamps match!
Name: Pelf TsDatatime: 2023-05-07-11:12:13.1260000 TsDatatime2: 2023-05-07-11:12:13.1260000

Read from database 2 - Real values from database, because it is new EF context===============
Not Found! Problem with rounding of timestamps caused mismatch.
Show list so to see what is there===============
Name: Pelf TsDatatime: 2023-05-07-11:12:13.1270000 TsDatatime2: 2023-05-07-11:12:13.1260000

And database state

We already saw that EF will in the first database query try to fill data from EF Cache and it creates the illusion that rounding works fine.

But, in 2nd case, rounding is a problem. In real query execution, rounding of dt1 is happening on the database side. But, when executing LINQ query in memory, there is no rounding of dt1. The result is timestamps mismatch, resulting in that the record can not be found in the collection, although the collection was “designed” to contain that particular record.

That is the “WEIRED” behavior that I saw first in my code. Because of rounding happening/not happening always, my records were not found. There is always a workaround, you can try to anticipate for possible rounding and search for records in the interval of [timestamp-2miliseconds, timestamp+2miliseconds].

6 Conclusion

The best solution is to avoid having SqlServer data type “datetime” in your database and always use “datatime2” which does not have accuracy and rounding problems.

But, if you have a legacy database with a lot of SqlServer data type “datetime” and you can not migrate all tables/columns to “datetime2” data type, you need to be careful about situations like in the above examples.