SqlServer和EF7：使用datetime与datetime2日期类型

SqlServer数据类型“datetime”存在准确性和舍入问题，并且已被没有此类问题的“datatime2”数据类型取代。但是，“datetime”仍然存在于许多旧数据库中。我们将展示.NET Entity Framework环境中的这些问题如何导致令人困惑的情况。

寒冰屋

2381人浏览 · 2024-01-18 21:52:05

寒冰屋 · 2024-01-18 21:52:05 发布

1 引言

2 SqlServer数据类型“datetime”与“datatime2”

下载源代码

1 引言

这一切都始于一个实际问题。我正在处理一些遗留的SqlServer数据库，当我注意到使用时间戳时的WEIRED行为时，我正在编写我的.NET/C#代码。这促使我更深入地探索问题，这就是这篇文章。最初，我在.NET 4.8 Framework/EF6环境中看到了问题，但本文中的示例是.NET7/EF7 Core环境和问题仍然存在。

主要问题是SqlServer数据类型“datetime”的准确性有限，并且数据库端正在发生舍入。较新版本的SqlServer数据类型“datetime2”具有更好的准确性，并且没有舍入问题。

在使用SqlServer数据类型“datetime”时，使用.NET Entity Framework只会造成混淆，因为当后台SQL查询在实际执行时会发生四舍五入，而在内存中执行LINQ时不会发生。此外，EF将尝试从EF缓存填充查询，因此看起来你的准确性可能比实际更高，因为与数据库中的实际数据相比，EF缓存中的数据具有更高的准确性。

2 SqlServer数据类型“datetime”与“datatime2”

基于[1]和[2]，下面是一个小表，概述了与本文相关的SqlServer数据类型“datetime”和“datatime2”之间的差异。

3 示例数据库

我们将尝试使用一些示C#代码来显示问题。为此，我们需要一个包含数据类型“datetime”和“datetime2”的小型数据库。这是我们的小型数据库，其中包含包含这两种数据类型的数据库表“People”。

4 C# 示例 1

为了演示问题，我们创建了C#。NET7测试应用程序并使用Entity Framework 7（一种数据库优先方法）（参见[3]）来访问数据库。

这是我们的程序。

下面是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"));
    }
}

这是执行结果：

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

和数据库状态

现在，从数据库表状态中可以明显看出准确性的损失和舍入问题。
有趣的是，EF将在第一个数据库查询中尝试从EF缓存填充数据，这会产生一种错觉，即我们的准确性比实际要高。

5 C# 示例2

第二个演示应用程序将显示在数据库查询中发生的舍入问题，但在内存中的LINQ查询中不会发生的舍入问题。

这是我们的程序。

下面是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"));
    }
}

这是执行结果：

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

和数据库状态