entity framework - underlying SQL statement

Question

Here’s a simple linq query I executed in Entity framework

    db.Responses.FirstOrDefault(r => r.QuestionId.Equals(“1.1.1”) && r.User.Id.Equals(user.Id)   && !r.IsDeleted);

Here QuestionId is a varchar datatype column, and db is the context object.

I fired up Entity profiler to see what was happening under the hood and underlying SQL query seem to have a bunch of statement that seemed a bit superfluous

/* 1 */    SELECT TOP (1) *
/* 2 */    FROM   [dbo].[Responses] AS [Extent1]
/* 3 */    WHERE  ((([Extent1].[QuestionId] = '1.1.1' /* @p__linq__0 */)
/* 4 */     AND (NOT ([Extent1].[QuestionId] IS NULL
/* 5 */                OR '1.1.1' /* @p__linq__0 */ IS NULL)))
/* 6 */     OR (([Extent1].[QuestionId] IS NULL)
/* 7 */         AND ('1.1.1' /* @p__linq__0 */ IS NULL)))
/* 8 */    AND ([Extent1].[UserId] = 1 /* @p__linq__1 */)
/* 9 */    AND (1 /* @p__linq__1 */ IS NOT NULL)
/* 10 */   AND ([Extent1].[IsDeleted] <> cast(1 as bit))

Notice all the extra code in lines 4-7, changing QuestionId to not null simplifies the query as so

/* 1 */    SELECT TOP (1) *
/* 2 */    FROM   [dbo].[Responses] AS [Extent1]
/* 3 */    WHERE  ([Extent1].[QuestionId] = '1.1.1' /* @p__linq__0 */)
/* 4 */    AND ('1.1.1' /* @p__linq__0 */ IS NOT NULL)
/* 5 */    AND ([Extent1].[UserId] = 1 /* @p__linq__1 */)
/* 6 */    AND (1 /* @p__linq__1 */ IS NOT NULL)
/* 7 */    AND ([Extent1].[IsDeleted] <> cast(1 as bit))

So, the question is why does entity framework put in all that extra code? Why is line 4 & line 6 necessary, the only relevant statements in the where clause are line 3,5 and 7,

I'm attempting to optimize my SQL statements and any thoughts on why entity framework is doing things this way would be helpful. I'm using EF6 with Visual studio 2013.

Answer 1

The lines:

/* 3 */    WHERE  ((([Extent1].[QuestionId] = '1.1.1' /* @p__linq__0 */)
/* 4 */     AND (NOT ([Extent1].[QuestionId] IS NULL
/* 5 */                OR '1.1.1' /* @p__linq__0 */ IS NULL)))

are supposed to account for the difference in semantics in null comparison between C#/VB.NET and SQL. You can control the behavior using the DbContext.Configuration.UseDatabaseNullSemantics or ObjectContextOptions.UseCSharpNullComparisonBehavior. You can find more details here and here.

Answer 2

That is because string '1.1.1' is passed as sql command parameter and while the SQL generator knows nothing about value of parameter. Ef does not generate different statements based on value. Statement has to be correct even if passed value is null.

Equals has to be true if both, column and parameter value are null when Column is nullable. And when it is not nullable, it can be true only if passed value is not null.

Every thing that EF does is 100% valid and it is implemented very correctly. Don't try to optimize unless you are getting wrong result.