SqlServer & EF7: 使用 datetime 与 datetime2 日期类型

Mark Pelf

5.00/5 (7投票s)

2023年6月9日

CPOL

3分钟阅读

7370

展示在 .NET7 EF7 环境中使用 SqlServer 数据类型 “datetime” 时精度受限的问题

下载源代码

1 引言

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

主要问题是 SqlServer 数据类型 “datetime” 的精度有限，以及在数据库端发生的舍入。较新版本的 SqlServer 数据类型 “datetime2” 具有更好的精度，没有舍入问题。

使用 .NET Entity Framework 只会加剧在使用 SqlServer 数据类型 “datetime” 时的困惑，因为数据舍入发生在后台 SQL 查询真正执行时，而不是在内存中执行 LINQ 时。此外，EF 将尝试从 EF 缓存中填充您的查询，因此您可能会认为您拥有比实际更好的精度，因为 EF 缓存中的数据比数据库中的真实数据具有更高的精度。

2 SqlServer 数据类型 “datetime” vs “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

数据库状态

我们已经看到 EF 将在第一个数据库查询中尝试从 EF 缓存填充数据，并产生舍入工作正常的错觉。

但是，在第二种情况下，舍入是一个问题。在实际的查询执行中，dt1 的舍入发生在数据库端。但是，在内存中执行 LINQ 查询时，dt1 没有舍入。结果是时间戳不匹配，导致在集合中找不到该记录，尽管该集合被“设计”为包含该特定记录。

这就是我最初在我的代码中看到的“奇怪”行为。由于总是发生/不发生舍入，我的记录未找到。总是有一种解决方法，您可以尝试预测可能的舍入，并在 [timestamp-2毫秒, timestamp+2毫秒] 的间隔内搜索记录。

6 结论

最好的解决方案是避免在您的数据库中使用 SqlServer 数据类型 “datetime”，并始终使用 “datatime2”，它没有精度和舍入问题。

但是，如果您有一个包含大量 SqlServer 数据类型 “datetime” 的遗留数据库，并且您无法将所有表/列迁移到 “datetime2” 数据类型，您需要小心处理上述示例中的情况。