CODEWITHSUNDEEP
c#sql-serverentity-frameworkdatetime
hyankov
hyankov

Reputation: 4130

How do I force Entity Framework to use datetime instead of datetime2, during DateTime comparison?

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

It generates a query like this:

([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))

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

I want EF to generate this:

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

With datetime:

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

With datetime2:

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

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

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

Upvotes: 6

Views: 5210

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

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 reports that column IntrdductionDate has properties (datetime, not null).

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);
         ...
    }

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));

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.

Upvotes: 5

Related Questions