Why EF6 LINQ does not generate proper "is null" SQL for null string variable comparison?

c# entity-framework-6 linq-to-entities

Question

We have an EDMX-based EF6 application, where we'd like to run a LINQ query like this.

string category = ... // comes from somewhere and equals to null
context.Product.Where(e => e.Category == category).ToArray();

The problem I see is that the generated SQL contains a [table].[Category] = @p... in the where clause, regardless of the variable category being null or not. So at the end of the day the query returns no results if the variable is null, instead of generating a proper is null criteria and return the proper rows.

I've tested with the explicit e => e.Category == null expression and that does generate the is null as expected.

I've also checked the EDMX and the SQL, and the corresponding Category column is nullable indeed in both places, so that might not be the issue.

Any help would be appreciated.

1
1
2/20/2020 10:36:06 AM

Accepted Answer

Try to set context.Configuration.UseDatabaseNullSemantics to false. This is the property which affects the mentioned behavior.

See the documentation here

1
2/20/2020 10:59:40 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow