LINQ Optimization eliminated Null values

Paul Denize 1 Reputation point
2021-03-29T02:07:16.03+00:00

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

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,954 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Cheong00 3,421 Reputation points
    2021-03-29T02:24:16.307+00:00

    [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.