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