LINQ query with a WHERE clause with multiple conditions

Question

I am using EntityFramework with POCOs.
Suppose I have POCOs that are defined as 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 get all SomeClass entries from a database that belong to one of Class1 , Class2 or Class3 , where ClassX.SomeNumber is equal to some number.

I wrote a LINQ query that 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 does not contain a record.

Decision

Apparently, I had to specify .Include instructions because lazy loading was disabled, and x.Class1 , x.Class2 and x.Class3 always null . I am ashamed because I clearly did not claim that lazy loading is disabled - the problem would be obvious then.

However, thanks to the Ladislav post, I improved my code as follows:

 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 did not know that LINQ-to-Entities should perform automatic code combining.

Answer 1

IMHO, you should be in order:

 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 the data, and after that you evaluate the condition in .NET = you must check the null value before accessing SomeNumber , but this is not necessary if you evaluate SomeNumber in SQL through Linq-to-entity. Linq-to-entity should do auto-zero combining.

Answer 2

According to your logic, if x.Class1 is not null, but x.Class1.SomeNumber is 3, it will not check all other sentences.

If you want to check just a little ClassN.SomeNumber == SomeNumber , then you should do it like this:

 int SomeNumber = 1; // some number List<SomeClass> retValue = result .Where(x => { if (x.Class1 != null && x.Class1.SomeNumber == SomeNumber) return true; else if (x.Class2 != null && x.Class2.SomeNumber == SomeNumber) return true; else if (x.Class3 != null && x.Class3.SomeNumber == SomeNumber) return true; return false; }) .ToList();