LINQ query with a WHERE clause with multiple conditions

Question

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.

Answer 1

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.

Answer 2

According to your logic, if x.Class1 is not null, but x.Class1.SomeNumber is 3, it won't check all the other clauses.

If you want to check, if just some 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();