LINQ query with a WHERE clause with multiple conditions

entity-framework, linq

Solution

IMHO you should be OK with just this:

Database DB = new Database(); 
var result = DB.SomeClass.Where(x =>
                            Number == x.Class1.SomeNumber ||
                            Number == x.Class2.SomeNumber ||
                            Number == x.Class3.SomeNumber)
                         .ToList();

Your query loads all data and after that you evaluate condition in .NET = you must test null value prior to accessing `SomeNumber` but that is not needed if you evaluate `SomeNumber` in SQL through Linq-to-entities. Linq-to-entities should perform automatic null coalescing.

Problem

I use EntityFramework with POCOs. Suppose I have POCOs defined like this (simplified): ``` class Class1 { public int ID; public int SomeNumber; } class Class2 { public int ID; public int SomeNumber; } class Class3 { public int ID; public int SomeNumber; } class SomeClass { public int ID; public int? Class1ID; public Class1 Class1; public int? Class2ID; public Class2 Class2; public int? Class3ID; public Class3 Class3; } ``` I want to fetch all `SomeClass` records from the database, that belong to an either one of `Class1`, `Class2` or `Class3` where `ClassX.SomeNumber` equals some number. I wrote the LINQ query which looks like this: ``` Database DB = new Database(); // object context var result = DB.SomeClass.ToList(); int SomeNumber = 1; // some number List<SomeClass> retValue = result .Where(x => { int Number = 0; if (x.Class1 != null) Number = x.Class1.SomeNumber; else if (x.Class2 != null) Number = x.Class2.SomeNumber; else if (x.Class3 != null) Number = x.Class3.SomeNumber; return Number == SomeNumber; }) .ToList(); ``` ...however `retValue` doesn't contain any record. The solution Apparently I had to specify `.Include` statements because lazy loading was disabled and `x.Class1`, `x.Class2` and `x.Class3` always had the `null` value. I feel ashamed because I didn't explicitly state that lazy loading was disabled - the problem would have been obvious then. However thanks to Ladislav's post, I improved my code like so: ``` Database DB = new Database(); // object context int SomeNumber = 1; // some number List<SomeClass> retValue = DB.SomeClass .Include("Class1") .Include("Class2") .Include("Class3") .Where(x => SomeNumber == x.Class1.SomeNumber || SomeNumber == x.Class2.SomeNumber || SomeNumber == x.Class3.SomeNumber) .ToList(); ``` I didn't know LINQ-to-Entities should perform automatic null coalescing.

Original source