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
This question is related to
linq
entity-framework
lazy-loading
Might be it will help someone, 4 level and 2 child's on each level
Library.Include(a => a.Library.Select(b => b.Library.Select(c => c.Library)))
.Include(d=>d.Book.)
.Include(g => g.Library.Select(h=>g.Book))
.Include(j => j.Library.Select(k => k.Library.Select(l=>l.Book)))
You might find this article of interest which is available at codeplex.com.
The article presents a new way of expressing queries that span multiple tables in the form of declarative graph shapes.
Moreover, the article contains a thorough performance comparison of this new approach with EF queries. This analysis shows that GBQ quickly outperforms EF queries.
There is no other way - except implementing lazy loading.
Or manual loading....
myobj = context.MyObjects.First();
myobj.ChildA.Load();
myobj.ChildB.Load();
...
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 is 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)
.Include(co => co.Employees)
.ThenInclude(emp => emp.Employee_Country)
With some notes
Employees.Employee_Car
and Employees.Employee_Country
), if you need to include 2 or more child properties of an intermediate child collection, you'll need to repeat the .Include
navigation for the collection for each child of the collection..ThenInclude
to preserve your sanity.Source: Stackoverflow.com