Linq query in Entity Framework multiple children entities include()
entity-framework lazy-loading linq
Question
This may be a really elementry question but whats a nice way to include multiple children entities when writing a query that spans THREE levels (or more)?
i.e. I have 4 tables: Company, Employee, Employee_Car and Employee_Country
Company has a 1:m relationship with Employee.
Employee has a 1:m relationship with both Employee_Car and Employee_Country.
If i want to write a query that returns the data from all 4 the tables, I am currently writing:
Company company = context.Companies
.Include("Employee.Employee_Car")
.Include("Employee.Employee_Country")
.FirstOrDefault(c => c.Id == companyID);
There has to be a more elegant way! This is long winded and generates horrendous SQL
I am using EF4 with VS 2010
Accepted Answer
Use extension methods. Replace NameOfContext with the name of your object context.
public static class Extensions{
public static IQueryable<Company> CompleteCompanies(this NameOfContext context){
return context.Companies
.Include("Employee.Employee_Car")
.Include("Employee.Employee_Country") ;
}
public static Company CompanyById(this NameOfContext context, int companyID){
return context.Companies
.Include("Employee.Employee_Car")
.Include("Employee.Employee_Country")
.FirstOrDefault(c => c.Id == companyID) ;
}
}
Then your code becomes
Company company =
context.CompleteCompanies().FirstOrDefault(c => c.Id == companyID);
//or if you want even more
Company company =
context.CompanyById(companyID);
Popular Answer
EF 4.1 to EF 6
There is a strongly typed .Include which allows the required depth of eager loading to be specified by providing Select expressions to the appropriate depth:
using System.Data.Entity; // NB!
var company = context.Companies
.Include(co => co.Employees.Select(emp => emp.Employee_Car))
.Include(co => co.Employees.Select(emp => emp.Employee_Country))
.FirstOrDefault(co => co.companyID == companyID);
The Sql generated in both instances is still by no means intuitive, but seems performant enough. I've put a small example on GitHub here
EF Core
EF Core has a new extension method, .ThenInclude(), although the syntax is slightly different:
var company = context.Companies
.Include(co => co.Employees)
.ThenInclude(emp => emp.Employee_Car)
...
As per the docs, I would keep the extra 'indent' in the .ThenInclude to preserve your sanity.
Obsolete Info (Don't do this):
The multiple grandchildren loading could be done in one step, but this requires a rather awkward reversal back up the graph before heading down the next node (NB: This does NOT work with AsNoTracking() - you'll get a runtime error):
var company = context.Companies
.Include(co =>
co.Employees
.Select(emp => emp.Employee_Car
.Select(ec => ec.Employee)
.Select(emp2 => emp2.Employee_Country)))
.FirstOrDefault(co => co.companyID == companyID);
So I would stay with the first option (one Include per leaf entity depth model).
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
