EntityFramework LINQToEntities generate weird slow TSQL Where-Clause

Question

I need to understand this. There is a big difference between EF5.0 and EF6.* in TSQL code-generation

In my code this is my LINQ - statemant

var qry2 = context.viw_overview_1.Where(i => i.article_EAN17 == ean).Select(i => i.article_id).Take(200);

EntityFramework 5.0 generate just a simple and fast TSQL WHERE - statement like this, which is perfect

... WHERE [Extent1].[article_EAN17] = @p__linq__0
00.0960096ms in SSMS

but EntityFramework 6.* generate a much complex and slow statement

... WHERE (([Extent1].[article_EAN17] = @p__linq__0) AND ( NOT ([Extent1].[article_EAN17] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[article_EAN17] IS NULL) AND (@p__linq__0 IS NULL))
45.3665362ms in SSMS

the field article_EAN17 has a index, too. however EF6.* takes ages anyway to initialize, BUT is there a way to generate a simple WHERE statement in EF6.* with attributes or something like this? I tried string.Equals() , string.Compare() , swaping the parameter, but nothing changed.

Why does Entity Framework 6 generate complex SQL queries for simple lookups? explain the difference, But is there a way to force EF generating simple TSQL.

I doubt the T-SQL change is slowing things down. Did you try both queries on the same dataset? — haim770, Apr 09 '14 at 14:12
Just curious - have you slapped the two statements into SSMS and compared their performance? — Eric Scherrer, Apr 09 '14 at 14:12
the simple comparison of EF5 is 5 times faster than EF6 in C# and much more faster in SSMS — Roland, Apr 09 '14 at 14:17
Part of the beauty of EF is that your not supposed to have to care about these things, or just care a little bit - otherwise the leaky abstraction sinks the ship. Other than some high level ways to control what happens, such as using .Include or .AsNoTracking if you have performance requirements that dictate the exact SQL used then consider using stored procedures. — Eric Scherrer, Apr 09 '14 at 14:23
Another thought - is article_EAN17 nullable because an outer join or because it is a nullable column in the DB? If the latter maybe you can not allow nulls in that column? — Eric Scherrer, Apr 09 '14 at 14:26
Is it not covered by this? https://entityframework.codeplex.com/workitem/145 — MattC, Apr 09 '14 at 14:26

mmilleruva · Accepted Answer · 2014-04-09T14:48:18.067

8

I believe this is related to your NULL comparison setting in Entity Framework

add the following code before your query to see if it helps your query performance:

context.ContextOptions.UseCSharpNullComparisonBehavior = true;

edited Apr 09 '14 at 14:48

answered Apr 09 '14 at 14:29

mmilleruva

2,110
18
20

1

Isn't it false by default? http://msdn.microsoft.com/en-us/library/system.data.entity.core.objects.objectcontextoptions.usecsharpnullcomparisonbehavior(v=vs.113).aspx – Corey Adler Apr 09 '14 at 14:41
3

actual it is `context.Configuration.UseDatabaseNullSemantics = true;` thx to MattC – Roland Apr 09 '14 at 14:42

score 1 · Answer 2 · answered Apr 09 '14 at 14:32

If you absolutely, positively need to have that added null check chopped off, you could always use DbSet.SqlQuery() (documentation here) to manually configure the query (and all the parameters) you want to it to run. Be careful, though, because sometimes that method can work in ways that you're not expecting. If you don't want/need any tracking you could also do use Database.SqlQuery<T>() (documentation here), which will allow you to use generics with your query as well (otherwise you'll have to cast it).

I personally would much rather either leave it alone, or use Stored Procedures, like @EricScherrer mentioned in the comments.

EntityFramework LINQToEntities generate weird slow TSQL Where-Clause

2 Answers2

Linked