CODEWITHSUNDEEP
c#.netentity-frameworklinqdatetime
Punit
Punit

Reputation: 1445

Enforce Entity Framework to use datetime than datetime2

We have EF 6.1 and it converts all .NET datetime fields to datetime2(SQL) when executing LINQ to SQL query, which is failing in certain scenarios due to date comparison. Is there a way that we can enforce EF to use datetime instead of datetime2. FYI column in DB is datetime. I have seen answers of enforcing all datetime to use datetime2 but couldn't find anything which keep datetime to datetime. Any particular reason that EF is converting datetime to datetime2.

Just to make it more clear that in DB column is Datetime, C# properties is declared as datetime. When EF converts the LINQ to SQL query it sends particular datetime field as datetime2 than datetime. Column Edate and Pdate are datetime and I pass a filter condition with datetime value but when i see in profiler it gets converted to datetime2. 

   exec sp_executesql N'SELECT TOP (1) 
[Project1].[ID] AS [ID]    
FROM ( SELECT 
    [Extent2].[ID] AS [ID]        
    FROM  [DBO].[TableB] AS [Extent1]
    INNER JOIN [DBO].[TableA] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
    WHERE ([Extent2].[EDate] <= @p__linq__1) AND ([Extent1].[Pdate] > @p__linq__2)
)  AS [Project1]
ORDER BY [Project1].[DateP] DESC',N'@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',
@p__linq__1='2018-06-24 16:43:34.7830000',@p__linq__2='2018-06-24 16:43:34.7830000'

Thanks, Punit

Upvotes: 7

Views: 5177

Answers (2)

Liviu Sosu
Liviu Sosu

Reputation: 1557

Rafael's answer is correct, howevre I would like to put a code-snippet here, in case some people might need it.

In your Context class (the one that inherits the DbContext from the Entity Framework library):

add this code:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
          modelBuilder.Entity<YourEntity>()
          .Property(p => p.YourDtaetimeColumn)
          .HasColumnType("datetime");
     }

Upvotes: 0

Rafael Botelho
Rafael Botelho

Reputation: 398

You need to install the Nuget Package: Microsoft.EntityFrameworkCore.SqlServer. After install, you can use HasColumnType("datetime") in your property. :)

Upvotes: 1

Related Questions