在DateTime比较期间，如何强制Entity Framework使用datetime而不是datetime2？ [英] How do I force Entity Framework to use datetime instead of datetime2, during DateTime comparison?

问题描述

我正在使用Entity Framework检索记录，并且其中一个过滤器是在 datetime 之前。

I am using Entity Framework for retrieving records and one of the filters is by datetime.

它会生成这样的查询：

([Extent1].[TxnDateTime] >= convert(datetime2, '2015-02-21 00:00:00.0000000', 121)) 
AND ([Extent1].[TxnDateTime] <= convert(datetime2, '2017-02-21 23:59:59.9999999', 121))

有没有办法将EF转换为 datetime datetime2 的时间？似乎要快得多。

Is there a way I can make EF convert to datetime instead of datetime2? It seems much faster.

我希望EF生成此代码：

I want EF to generate this:

[Extent1].[TxnDateTime] >= convert(datetime, '21/02/2015')  
AND [Extent1].[TxnDateTime] <= convert(datetime, '21/02/2017')

具有 datetime ：

CPU时间= 1234 ms，经过的时间= 1250 ms。

CPU time = 1234 ms, elapsed time = 1250 ms.

使用 datetime2 ：

CPU时间= 1625毫秒，经过时间= 1645 ms。

CPU time = 1625 ms, elapsed time = 1645 ms.

我知道.NET DateTime 类型映射到SQL Server datetime2 。我有什么选择？

I understand that the .NET DateTime type maps to SQL Server datetime2. What are my options though?

该列可为 datetime 为空，我正在将其与 DateTime？

The column is nullable datetime and I am comparing it to DateTime?

推荐答案

嗯，有趣！当我创建一个带有 DateTime 列的表的简单数据库模型时，默认情况下，Entity Framework创建的列具有SQL DateTime类型，而不是DateTime2。我使用的是EntityFramework版本6.1.3

Hmm, Interesting! When I create a simple database model with a table with a DateTime column, Entity Framework by default creates a column with a SQL DateTime type, not DateTime2. I used EntityFramework version 6.1.3

class Blog
{
     public int Id {get; set;}
     public DateTime IntroductionDate {get; set;}
}

Sql Server Management Studio报告列 IntrdductionDate 具有属性（日期时间，不为null）。

Sql Server Management Studio reports that column IntrdductionDate has properties (datetime, not null).

无论如何，前一段时间我有一个问题，我希望每个DateTime都建模为 dateTime2 而不是 datetime 。我想您可以使用类似的方法来强制该列使用 datetime

Anyway, A while ago I had the problem that I want every DateTime to be modeled as a dateTime2 instead of a datetime. I guess you could use a similar method to force the column to use datetime

class MyDbContext : DbContext
{
    DbSet<...> ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         // Configure that all used DateTime should be modeled datetime2:
         const string standardDateTimeType = "datetime2";
         modelBuilder.Properties<DateTime>()
             .Configure(p => p.HasColumnType(standardDateTimeType);
         ...
    }

您可以将其用于所有要建模的类型，例如，如果要建模，则所有小数都具有相同的Precision和Scale ：

You can use this for all types that you want to model. For instance if you want to model that all decimals have the same Precision and Scale:

byte standardDecimalPrecision = 19;
byte standardDecimalScale = 8;
modelBuilder.Properties<decimal>()
    .Configure(p => p.HasPrecision(standardDecimalPrecision, standardDecimalScale));

当然，您可以使用 ColumnAttribute 数据注释设置列的类型，但这将迫使您对每个DateTime进行此操作，否则将来的类可能会忘记这些错误这个。

Of course you could set the type of a column using ColumnAttribute data annotations, but that would force you to do this for every DateTime, with the possible errors that future classes would forget this.

这篇关于在DateTime比较期间，如何强制Entity Framework使用datetime而不是datetime2？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！