目录
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和数据库状态
我们已经看到，EF将在第一个数据库查询中尝试从EF缓存填充数据，并产生舍入工作正常的错觉。
但是，在第二种情况下，四舍五入是一个问题。在实际查询执行中，dt1的舍入发生在数据库端。但是，在内存中执行LINQ查询时，没有dt1的舍入。结果是时间戳不匹配，导致在集合中找不到该记录，尽管该集合被“设计”为包含该特定记录。
这是我在代码中首先看到的“WEIRED”行为。由于四舍五入发生/不总是发生，因此找不到我的记录。总有一种解决方法，您可以尝试预测可能的舍入，并在[timestamp-2miliseconds，timestamp+2miliseconds]的间隔内搜索记录。
6 结论
最好的解决方案是避免在数据库中使用SqlServer数据类型“datetime”，并始终使用“datatime2”，它没有准确性和舍入问题。
但是，如果您有一个具有大量SqlServer数据类型“datetime”的旧数据库，并且无法将所有表/列迁移到“datetime2”数据类型，则需要注意上述示例中的情况。
7 参考资料：
- [1] datetime (Transact-SQL) - SQL Server | Microsoft Learn
- [2] datetime2 (Transact-SQL) - SQL Server | Microsoft Learn
- [3] Entity Framework 7 – Database First – Using EFCorePowerTools - CodeProject
https://www.codeproject.com/Articles/5362403/SqlServer-EF7-Using-datetime-vs-datetime2-date-typ
5862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
