EF hard coded value in WHERE clause is fast, string parameter is slow

user_1369298 0
Viewed 331

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

entity-framework c#
2 Answers

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;
user_73988190

As you can see, when you use a parameter, EF adds a null comparison check, to mimic the behavior of null comparison in C#. While in C#, null == null is True, in SQL, NULL = NULL is False. Depending on the size of your table, this could affect your performance.

You have two options here:

  1. The preferred one in my opinion is to add an index to the table on the SourceDealerID column. Note that an index has a performance impact on updates and inserts, so you should test that.
  2. Disable the null check, and remember that your LINQ query behaves like SQL and not like C#. This can be done by setting UseDatabaseNullSemantics to true.
user_7674250
Related