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.

Answer 1

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;

Answer 2

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.