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

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.

Is thre any reason you do not do the null check on the LINQ side? I mean, it is not like you can only have one WHERE statement. You do know you can chain them, also dynamically? My standard is basically to do the null check then only add the where clause when t is not null. Otherwise this DOES Look like a bug - open tickets on the github side. At minimum they should generate IS NULL. — TomTom
– TomTom, Commented Feb 20, 2020 at 10:33
The expectation is wrong, would violate SQL's three-valued logic, and actually surprise developers that expected a null value to return nothing. As you posted, EF (and most ORMs) generate parameterized queries. The queries don't change each time a value changes. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 20, 2020 at 10:33
Category = @p1 is very different from Category = @p1 or Category IS NULL too, resulting in very different execution plans and performance. EF (or any ORM) can't arbitrarily choose the wider query over the narrower one, as it would return unexpected results and result in far worse performance. You could argue there should be a switch for people that expect an ORM to not use SQL semantics, but that would result in a maintenance nightmare when other developers try to understand why a query doesn't behave as expected — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 20, 2020 at 10:38
@PanagiotisKanavos thank you for the clarification, it makes sense. However all I've expected is EF to be clever, so that it would notice when parsing my LINQ expression that the expression category is basically null so it should generate a [Category] is null where clause this case. It seems like EF does not evaluate such expressions locally but just generate a parameterized query. — Zoltán Tamási
– Zoltán Tamási, Commented Feb 20, 2020 at 10:42
@ZoltánTamási I would expect EF (or any ORM) to not violate SQL's behavior. You're asking for EF to use C#'s NULL logic instead of SQL's, even though it's clearly querying a SQL database. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 20, 2020 at 10:44

Zoltán Tamási · Accepted Answer · 2020-02-20 10:59:40Z

2

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

See the documentation here

edited Feb 20, 2020 at 10:59

Zoltán Tamási

12.9k8 gold badges71 silver badges101 bronze badges

answered Feb 20, 2020 at 10:56

Urbancsik Gergely

362 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related