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