CODEWITHSUNDEEP
c#sql-serverentity-framework-6
LittleWhiteFairy
LittleWhiteFairy

Reputation: 97

Query in Entity Framework takes longer than direct sql

The main problem is that when I execute a certain query in SQL Management studio it takes about 2 seconds. When performed through entity framework and I look at it with a profiler the query takes 260 seconds. The result is only 72 rows of 1 integer. This query is built from a filter that the user defines.

Does anyone have anyone suggestions what might be wrong? The sqlquery I can see in the profiler is the following:

SELECT [Distinct1].[PersonID] AS [PersonID]
FROM   (SELECT DISTINCT [Extent1].[PersonID] AS [PersonID]
         FROM   (SELECT [PersonViewWithExtraInfo].[PersonID]           AS         [PersonID],
                   [PersonViewWithExtraInfo].[DateTime]             AS [DateTime],
                   [PersonViewWithExtraInfo].[ExtraInfo1]         AS [ExtraInfo1],
                   [PersonViewWithExtraInfo].[ExtraInfo2]           AS [ExtraInfo2],
                   [PersonViewWithExtraInfo].[ExtraInfo3]               AS [ExtraInfo3],
                   [PersonViewWithExtraInfo].[ExtraInfo4] AS [ExtraInfo4]
            FROM   [core].[PersonViewWithExtraInfo].AS [PersonViewWithExtraInfo].AS [Extent1]
           LEFT OUTER JOIN [core].[Persons] AS [Extent2]
             ON [Extent1].[PersonID] = [Extent2].[PersonID]
    WHERE  ([Extent1].[ExtraInfo4] = 1)
           AND(([Extent2].[FirstName] = 'Steven' /* @p__linq__0 */)
                 OR (([Extent2].[FirstName] IS NULL)
                     AND ('Steven' /* @p__linq__0 */ IS NULL))
                 OR ([Extent2].[FirstName] = 'Steffi' /* @p__linq__1 */)
                 OR (([Extent2].[FirstName] IS NULL)
                     AND ('Steffi' /* @p__linq__1 */ IS NULL))
                 OR ([Extent2].[FirstName] = 'Evy' /* @p__linq__2 */)
                 OR (([Extent2].[FirstName] IS NULL)
                     AND ('Evy' /* @p__linq__2 */ IS NULL)))
           AND ([Extent1].[DateTime] >= '2014-06-01T00:00:00' /* @p__linq__3 */)
           AND ([Extent1].[DateTime] <= '2015-05-31T00:00:00' /* @p__linq__4 */)) AS [Distinct1]

I build my where clause for comparing a string like this:

public static Expression<Func<string, bool>> ConvertToExpression(this FilterComparer comparer, string compareValue)
  {
     switch (comparer)
     {
        case FilterComparer.IsEqualTo:
           return x => x == compareValue;
...

EDIT: I found where the problem is. When I took the query from a sqlprofiler the query was surrounded with an exec sp_executesql and when I pasted this in management studio the query took as long as with EF. So when I removed the null checks in this query the delay was gone. I should have listened to the EF profiler when he said 'too many expressions per where clause'. Only thing is the null checks are added by entity framework. How can I prevent that?

EDIT: This is the linq-query I get from Linq Insight.

(from x in     ((ObjectQuery<PersonViewWithExtraInfo>)PersonViewWithExtraInfo).MergeAs(0)
where x.ExtraInfo4 && (((False || (True && (x.Person.FirstName     == compareValue))) || (True && (x.Person.FirstName == compareValue))) || (True     && (x.Person.FirstName == compareValue)))
where (x.DateTime >= startDate.Date) && (x.DateTime <= endDate.Date)
select x.PersonID).Distinct()

The true's and false's are there because the query is built dynamically according to a filter the user defines.

Upvotes: 2

Views: 672

Answers (1)

LittleWhiteFairy
LittleWhiteFairy

Reputation: 97

So the problem was that EF was adding null checks to the query and this made the where-clause to big. By setting 

Configuration.UseDatabaseNullSemantics = true;

on the DbContext the problem was solved!

Upvotes: 2

Related Questions