2019-11-08
EF hard coded value in WHERE clause is fast, string parameter is slow
stackoverflow
Question

I am experiencing a very slow performance when I set a string variable in my EF Where clause and very fast performance when I hard code the string value in the where clause.

C#, .Net Framework 4.7.1, EF 6.2

50ms

db.Dealers.Where(x => x.SourceDealerId == "000111222fff333q");

1.5seconds

var parameter = "000111222fff333q";
db.Dealers.Where(x => x.SourceDealerId== parameter );

The dealerId is of type Char(18) in the Database and it is nullable. The approach is Database first. Here are the properties of the dealer column: enter image description here

There is difference in the SQL that is generated in both cases. For the fast case: ...WHERE dealerId = '000111222fff333q'

For the slow case:

enter image description here

Answer
1

This might be related to NULL comparison setting in Entity Framework

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

context.ContextOptions.UseCSharpNullComparisonBehavior = false;
EF hard coded value in WHERE clause is fast, string parameter is slow
See more ...