question

PaulDenize-4562 avatar image
0 Votes"
PaulDenize-4562 asked Viorel-1 edited

LINQ Optimization eliminated Null values

I built a LINQ query then added
.Where(a=>a.property!="This")

As expected it produced SQL
Where (a.propery<>N'This') OR a.property is NULL
This eliminated the string selected but retained all null values - Good.

However when I added another restriction
.Where(a=>a.property!="That")

The SQL appeared to be optimized
Where (a.propery not in (N'This',N'That')

Problem is it also eliminated all the NULL values this time.
(The clause OR a.property is NULL was dropped)

I am using checkboxes on the GUI to decide which values to exclude. Problem is selecting more than one and all the Null values get excluded too.



I tried
.Where(a=>a.property!="Other" || a.property is NULL)
but the SQL is verbose and after several statements like this are added the SQL is Extremely verbose.

WHERE ([a].[property] IS NULL OR (([a].[property] <> N'This') OR [a].[property] IS NULL)) AND ([a].[property] IS NULL OR (([a].[property] <> N'That') OR [a].[property] IS NULL)) OR [a].[property] IS NULL)) AND ([a].[property] IS NULL OR (([a].[property] <> N'Other') OR [a].[property] IS NULL))



Shouldn't
.Where(a=>a.property!="This")
.Where(a=>a.property!="That")
.Where(a=>a.property!="Other")

give
WHERE [a].[property] NOT IN (N'This',N'That',N'Other') OR [a].[property] IS NULL

dotnet-csharp
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Some articles recommend using Where(a=>!object.Equals(a.property, "This")) instead of ‘!=’.

You can also build a list of all of selected values and use a single Where(a=>!mylist.Contains(a.property)), maybe also adding || a.property == null.

Check if these suggestions have effect.

0 Votes 0 ·

1 Answer

cheong00 avatar image
1 Vote"
cheong00 answered cheong00 commented

[quote]
I built a LINQ query then added
.Where(a=>a.property!="This")

As expected it produced SQL
Where (a.propery<>N'This') OR a.property is NULL
This eliminated the string selected but retained all null values - Good.
[/quote]

IMO if that is the behavior exhibited, then it is wrong in the beginning. .Where(a=>a.property!="This") shouldn't generate code OR a.property is NULL at the end. You may want to verify again and see if this is bug in your LINQ to SQL engine.

======

As for your question, .Where(a=>a.property!="Other" || a.property is NULL) is the proper way. You may want to post the whole LINQ query and let us check what could possibly caused the query to have that many redundant part. You'll also want to specify your .NET framework version and LINQ library version for us the check.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't know that I agree - Null is not equal to "This" so I would expect it to be returned.
The statement has requested one value to be excluded not two.

.NetCore 2.2.0
System.Linq 4.3.0

I'm sorry I cannot post the full example. I had to Obfuscate the example from Production Code to this.
But the actual code was pretty simple table + optional sequence of above where clauses. The SQL was obtained by taking the resulting queriable and doing a Visual Studio Immediate execution including .Count() to force evaluation and emit the SQL.

0 Votes 0 ·
cheong00 avatar image cheong00 PaulDenize-4562 ·

Oh, I misread it for the cause of "=", apologies for that. (Need more coffee...)

For your issue, you may want to add an issue to their GitHub page and see what they can do with it.


0 Votes 0 ·