Linq multiple where clause with no or

Question

I am trying to do a multiple where clause in linq that looks like the following:

.Where(x => x.Division == item.Division)
.Where( x => x.ProductID == item.ProductID)
.Where( x => item.supplierNumber == item.supplierNumber)
.Where(x => item.OpcoID == item.OpcoID);

when I spit out the SQL it looks like this

WHERE  ( ( [extent1].[Division] = @p__linq__0 ) 
      OR ( ( [extent1].[Division] IS NULL ) 
           AND ( @p__linq__0 IS NULL ) ) ) 
   AND ( ( [extent1].[ProductID] = @p__linq__1 ) 
          OR ( ( [extent1].[ProductID] IS NULL ) 
               AND ( @p__linq__1 IS NULL ) ) ) 
   AND ( ( @p__linq__2 = @p__linq__3 ) 
          OR ( ( @p__linq__2 IS NULL ) 
               AND ( @p__linq__3 IS NULL ) ) ) 
   AND ( ( @p__linq__4 = @p__linq__5 ) 
          OR ( ( @p__linq__4 IS NULL ) 
               AND ( @p__linq__5 IS NULL ) ) )

notice the

[Extent1].[Division] = @p__linq__0) OR (([Extent1].[Division] IS NULL)

I don't want it to include the or I just want it to be

where Division = @p_Linq_0 and [Extent1].[ProductID] = @p_Linq_1

What am I doing wrong ?

try combining all criteria into one where clause, chained with the && operator. the third and fourth, `item.supplierNumber == item.supplierNumber` are probably by mistake. — Cee McSharpface, Apr 11 '18 at 19:55
Try to set context.Configuration.UseDatabaseNullSemantics to true. — Evk, Apr 11 '18 at 19:55
FYI, those last two clauses will always be `true` or throw a `NullReferenceException` — Rufus L, Apr 11 '18 at 19:56
I put it in one where clause and chained with && and it does the same — Micah Armantrout, Apr 11 '18 at 19:56
You are not doing anything wrong, LINQ generates expressions that handle all possible cases, and since LINQ / C# currently defaults to any object possibly being `null` and `null` = `null` is false in SQL but true in C#, it has to add the extra test to handle that case. Perhaps when C# has non-nullability they can improve LINQ to not test for that and confusing users instead. — NetMage, Apr 11 '18 at 19:59
I think my mistake is item.supplierNumber == item.supplierNumber — Micah Armantrout, Apr 11 '18 at 19:59
As @RufusL says, also `item.OpcoID == item.OpcoID` will be trivially true. — NetMage, Apr 11 '18 at 20:00

Jon Skeet · Accepted Answer · 2018-04-11T19:59:18.280

Unless the value you're comparing with is null, it'll be fine. Notice the bracketing:

(
 ([Extent1].[Division] = @p__linq__0) OR
 (([Extent1].[Division] IS NULL) AND (@p__linq__0 IS NULL))
)

Basically it's ensuring that null values are compared as they are in C#, where x == y will evaluate to true if both x and y are null references. In SQL, that wouldn't evaluate to true, because null values aren't considered to be equal.

LINQ aims to emulate the C# code you write, which is why it's checking for nullity like this... but if the value you pass in isn't null, it won't match anything with a null Division value.

As Evk says, if you're using Entity Framework, you can use the UseDatabaseNullSemantics property in your configuration to avoid this:

Gets or sets a value indicating whether database null semantics are exhibited when comparing two operands, both of which are potentially nullable. The default value is false. For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

Linq multiple where clause with no or

1 Answers1