CODEWITHSUNDEEP
c#sql.netentity-frameworkedmx
Guillaume Philipp
Guillaume Philipp

Reputation: 195

Entity Framework EDMX force DateTime2

I'm working on some Database optimisation and I found that one of my main issue is that some queries are way slower using "exec sp_executesql" than raw SQL. I'm thinking this has something to do with implicite datetime/datetime2 conversion because all the slow query are comparing dates.

At the end of the "exec sp_executesql" there is always something like that :

)))',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7)',@p__linq__0='2015-01-09 18:52:46.9264744',@p__linq__1='2015-01-09 18:52:16.926',@p__linq__2='2015-01-09 18:52:16.926',@p__linq__3='2015-01-09 18:52:16.926',@p__linq__4='2015-01-09 18:52:16.926',@p__linq__5='2015-01-09 18:52:16.926',@p__linq__6='2015-01-09 18:52:16.926',@p__linq__7='2015-01-09 18:52:16.926',@p__linq__8='2015-01-09 18:52:16.926';

In my database .NET DateTime are stored as datetime. My model is generated from my EDMX.

Here is my question, should I change all my datetime fields or can I avoid this useless implicite conversion ?

Thanks !

Upvotes: 0

Views: 553

Answers (1)

cann0nball
cann0nball

Reputation: 26

I can confirm this behaviour (EF 6.1.0). The problem is the EF translated query prevents SQL server from using indexes (if your DateTime columns are indexed). Example:

exec sp_executesql N'... WHERE ([Extent1].[Timestamp] >= @p__linq__0) AND ([Extent1].[Timestamp] <= @p__linq__1)',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2014-03-01 0:00:00',@p__linq__1='2015-03-01 0:00:00'

Notice the difference between query condition(datetime) and parameters datatype(datetime2).

A quick workaround is to declare your DateTime parameters as nullable, which adds an additional CAST to the translated query conditions, indexes are used.

exec sp_executesql N'...WHERE ( CAST( [Extent1].[Timestamp] AS datetime2) >= @p__linq__0) AND ( CAST( [Extent1].[Timestamp] AS datetime2) <= @p__linq__1)',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2014-03-01 0:00:00',@p__linq__1='2015-03-01 0:00:00'

This was supposedly fixed in 6.1.2, didn't have time to confirm it yet.

Upvotes: 1

Related Questions