WHERE clause with many criteria in a LINQ query

entity-framework linq

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.

1
7
4/26/2012 1:40:08 PM

Accepted Answer

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.

4
4/26/2012 1:05:06 PM

Popular Answer

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();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow