Entity Framework和DateTime的毫秒精度比较

Question

我在使用C#的Entity Framework（Code First）时遇到了一个问题，涉及DateTime值的比较。 我使用下面定义的类Validity（本例中进行了简化）作为其他实体类的超类，这些实体类应该在时间上具有定义的有效性。

public abstract partial class Validity {
    [Key]
    public int ID { get; set; }

    public DateTime? ValidFrom { get; set; }
    public DateTime? ValidTo { get; set; }

    /**
     * @brief This method builds an IQueryable from another IQueryable,
     * with added restriction on ValidityFrom/To
     *
     * An object's validitiy is defined to
     *   1. start at timestamp ValidFrom (=inclusive) and
     *   2. to end before ValidTo (=exclusive).
     *   3. If ValidFrom or ValidTo is NULL, it means to be "unbounded"
     *      in start or end time (respectively)
     * 
     **/
    public static IQueryable<T> isValidAt<T>(IQueryable<T> query, DateTime time) where T : Validity
    {
        return query.Where<T>(c => 
               (!c.ValidFrom.HasValue || time >= c.ValidFrom)  // If ValidFrom != NULL, the given timestamp must be equal or "after" ValidFrom
            && (!c.ValidTo.HasValue || time < c.ValidTo));     // If ValidTo != NULL, the given timestamp must be "before" ValidTo
    }

    /**
     * @brief Shall invalidate the object at timestamp time (implicitly sets validTo attribute).
     **/
    public void inValidate(DateTime time)
    {
        ValidTo = time;
    }
}

public class Item : Validity {
    public string property { get; set; }
}

在最后三行，你会发现一个名为“Item”的类，我们将以此作为示例。让我们看一下这个查询：

DateTime requestTime = DateTime.Now;
var items = from n in Validity.isValidAt(db.Items, requestTime)
            select n;

这个查询应该只返回在“requestTime”时“有效”的Item类对象。请注意，对于ValidTo == requestTime的情况，Item类应被视为“无效”（ValidFrom到ValidTo的时间跨度是-排除-ValidTo;请参见上面源代码中的注释）。

问题

实际上，我的结果集“items”中有一些ValidTo == requestTime的结果。我通过以下方式进行了检查：

Item i= items.FirstOrDefault();
if ((i.ValidFrom.HasValue && i.ValidFrom > requestTime)
 || (i.ValidTo.HasValue && requestTime >= i.ValidTo)) {

   // ... SOME ERROR OUTPUT ...

}

**注意：这个错误并不是偶尔发生，而是在软件中几乎每次调用.inValidate(requestTime)来使对象失效时都会出现。**

我通过Microsoft SQL Server Management Studio（使用Microsoft SQL Server 2008作为后端）手动检查了由LinQ生成的SQL查询。我不得不自己声明/设置@p__linq__0，@p__linq__1（它们都表示requestTime）...

DECLARE @p__linq__0 DATETIME
DECLARE @p__linq__1 DATETIME
SET @p__linq__0 = '2012-10-23 15:15:11.473'
SET @p__linq__1 = '2012-10-23 15:15:11.473'

这实际上按预期工作。但是如果我使用'2012-10-23 15:15:11'作为值，我将收到错误结果（如预期）。它们与我的程序中的结果相似。所以我想这就是问题所在...

在数据库中，“DateTime”定义了毫秒，并且ValidFrom / ValidTo包括毫秒存储。但是，我认为查询由于某种原因不包括时间戳的毫秒部分...然而，变量requestTime设置了毫秒值。

不幸的是，我不知道如何检查查询中发送的实际值以验证这一点。我只知道如何使用items.toString（）-Method输出生成的SQL，其中包含占位符。

我尝试过： 1. db.Log = Console.Out;，由于出现“db.Log未定义”的错误（自动完成也没有建议“Log”），无法编译。而db是从DbContext派生的。 2. 将“items”强制转换为ObjectQuery，然后使用.ToTraceString（）也无效，程序在运行时崩溃并显示类型转换无效的错误消息。

如果这很重要：我使用.NET 4.0和EntityFramework.5.0.0。

问题：

1. 如何记录/输出完整的SQL（包括占位符的值）？
2. 如何优雅地解决这个问题？...我不是指一个仅仅从“time”中减去一秒钟的hack，然后将其分配给“ValidTo”in inValidate（）！

最好的问候，

Stefan

编辑（发现更多细节）

我通过SQL分析器检查了发生了什么，看起来很好。查询时正确提供高（7位数）精度的时间戳。但是：我没有得到导致错误结果的SELECT。所以我猜测：这必须是一些缓存。因此，我在我的LINQ查询之前直接放置了db.SaveChanges（）;。现在我在分析器中得到了所有查询。

我尝试了以下代码来更改数据库中的数据类型。正如Slauma建议的那样（请参见https://dev59.com/nmsz5IYBdhLWcg3wOVL5#8044310）。

modelBuilder.Entity<Item>().Property(f => f.ValidFrom)
  .HasColumnType("datetime2").HasPrecision(3);
modelBuilder.Entity<Item>().Property(f => f.ValidTo)
  .HasColumnType("datetime2").HasPrecision(3);

在重新启动之前，我删除了整个数据库...

结果：使用HasPrecision（x）没有成功；其中x是0、3中的一个（有或没有db.SaveChanges()直接在之前）；但是：x = 7在查询之前直接使用db.SaveChanges();比较有效...

所以，不幸的是，这个问题仍然存在...

当前解决方法

我对任何DateTime值应用以下方法，然后再将其分配给数据库对象属性。它只是将DateTime舍入到完整的秒精度（我在数据库中进行了配置）。对于任何用于比较的DateTime也会应用此方法。

结果：这更像是一种hack而不是解决方案！我需要为所有setter方法编写访问函数，以便不能意外地进行直接赋值。

    public static DateTime DateTimeDBRound(DateTime time) {
        DateTime t = time;
        long fraction = (t.Ticks % TimeSpan.TicksPerSecond);
        if (fraction >= TimeSpan.TicksPerSecond / 2)
        {
            t = t.AddTicks(TimeSpan.TicksPerSecond - fraction);
        }
        else
        {
            t = t.AddTicks(-fraction);
        }
        return t;
    }

- SDwarfs

---

即使提到的文章似乎是关于同样的问题，它的解决方案并不起作用。没有 .edmx 文件。我猜这是因为我使用了 Code First 方法。无论如何，我将更详细地调查此文章的解决方案。如果可以以某种方式解决，我将确认删除请求（或只需自己删除）。 - SDwarfs

参数requestTime的精度应该比毫秒高得多，即datetime2(7)（即100皮秒精度）：http://stackoverflow.com/a/11620980/270591。这个链接讨论了在*存储*.NET DateTime时精度损失的问题。但是你的结果很奇怪，DB中x<y，但在内存中实际结果却是x>=y，这听起来像是一个bug。你可以尝试使用datetime2(7)作为DB类型（这是.NET DateTime的确切表示），而不是datetime，但我认为这不是必要的。 - Slauma

如果您不知道如何将DateTime属性映射到具有EF Code-First的datetime2（7）列类型，请参考以下链接：https://dev59.com/nmsz5IYBdhLWcg3wOVL5#8044310 - Slauma

你是否尝试过按照文章中提到的将DateTime的精度设置为3？你可以在重写的OnModelCreating方法中使用HasPrecision Fluent Api方法来实现（http://msdn.microsoft.com/en-us/library/system.data.entity.modelconfiguration.configuration.datetimepropertyconfiguration.hasprecision(v=vs.103).aspx）。这里是一个链接，介绍如何使用Fluent API配置模型。http://msdn.microsoft.com/en-US/data/jj591617 - Pawel

是的，就像在“编辑（更多细节）”下找到的一样（见上文）？---注意：我现在用字符串表示（“YYYY-MM-DD HH:MM:SS”格式）替换了DateTime。这更像是一个hack，但是有效。 - SDwarfs

Answer 1

问题1：如何记录/输出完整的SQL（包括占位符的值）？ 我认为最好的方法是使用SQL Server分析器。它显示所有语句和值。 或者http://www.hibernatingrhinos.com/products/EFProf 我不知道其他提取执行命令的方法。

- Steven Spyrka