WHERE clause with many criteria in a LINQ query

entity-framework linq

Question

With POCOs, I use EntityFramework.
Assume my POCOs are specified as follows (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 gather everySomeClass data from the database that is a part of one ofClass1 , Class2 or Class3 where ClassX.SomeNumber equals a certain quantity

This is how the LINQ query I created looks:

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 contains no records at all.

The answer

I guess I had to be specific.Include due to the fact that lazy loading was deactivated andx.Class1 , x.Class2 and x.Class3 possessed thenull value. I regret not clearly stating that lazy loading was deactivated since it would have made the issue clear.

However, as a result of Ladislav's article, I enhanced 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 had no idea that LINQ-to-Entities should automatically coalesce null values.

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

Accepted Answer

I think you can live with only 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 the data, and you then assess the condition in.NET, which states that you must verify the null value before accessingSomeNumber nevertheless, if you assess, you won't need that.SomeNumber via Linq-to-entities in SQL. Automatic null coalescing should be performed via linq-to-entities.

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

Popular Answer

Your reasoning states that the other clauses won't be checked if x.Class1 is not null but x.Class1.SomeNumber is 3.

If you want to determine if simply someClassN.SomeNumber == SomeNumber then you should proceed as follows:

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