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.

@WiktorZychla r.QuestionId == "1.1.1" makes no difference and neither should it - both .Equals and "==" are equivalent, with r.QuestionId == "1.1.1" the code remains the same. — Sangeet Agarwal
– Sangeet Agarwal, Commented Dec 22, 2013 at 16:21

Community · Accepted Answer · 2017-05-23 12:28:41Z

3

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.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Dec 21, 2013 at 21:14

Pawel

31.7k4 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akash Kava · Accepted Answer · 2013-12-21 20:22:30Z

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.

edited Dec 21, 2013 at 20:22

answered Dec 21, 2013 at 20:17

Akash Kava

40.1k21 gold badges126 silver badges173 bronze badges

2 Comments

educampver Over a year ago

I don't think it implies a great performance improvement, but is there any way of avoiding EF to generate these somehow naive statements?

Akash Kava Over a year ago

@e.campver performance is useless if logic is wrong, EF generates statements with explicit null checks will perform better as query analyzer will omit extra condition checks for null values.

Collectives™ on Stack Overflow

entity framework - underlying SQL statement

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related