Linq query in Entity Framework multiple children entities include()

Question

Linq query in Entity Framework multiple children entities include()

entity-framework lazy-loading linq

Question

What is a great method to add numerous children entities when crafting a query that spans THREE levels (or more)? This may be a really basic question.

I have four tables, so:Company , Employee , Employee_Car and Employee_Country

Employee and Company are in a 1:1 relationship.

The association between Employee and Employee Car and Employee and Employee Country is 1:m.

I'm presently writing the following query if I want to get data from all four tables:

Company company = context.Companies
                         .Include("Employee.Employee_Car")
                         .Include("Employee.Employee_Country")
                         .FirstOrDefault(c => c.Id == companyID);

There must be a more tasteful approach! This is verbose and produces terrible SQL.

With VS 2010, I'm use EF4.

Nathan Liu

Popular Answer

EF 4.1–EF 6

Using select expressions at the proper depth, a firmly typed.Include enables the necessary depth of eager loading to be specified:

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 that was created in both cases is still not at all clear, but it seems to be sufficiently efficient. I provided a little illustration on Here is GitHub.

Core EF

Although the syntax is a little distinct, EF Core includes a new extension method, .ThenInclude():

var company = context.Companies
                     .Include(co => co.Employees)
                           .ThenInclude(emp => emp.Employee_Car)
                      ...

According to the documents, I would maintain the additional "indent".ThenInclude to keep you from going crazy.

Old Information (Don't Do This):

could may load several grandchildren in a single step, however doing so necessitates an uncomfortable loopback up the graph before continuing down the next node (NB: This does NOT work with multiple grandkids).AsNoTracking() - a runtime error will occur)

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'd stick with the original choice (one Include per leaf entity depth model).

StuartLC

Related Questions

View more on Stack Overflow

Nix · Accepted Answer · 10/22/2012 5:03:03 PM

Apply zzz-5 zzz. Substitute the name of your object context for NameOfContext.

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

}

Your code then becomes

     Company company = 
          context.CompleteCompanies().FirstOrDefault(c => c.Id == companyID);

     //or if you want even more
     Company company = 
          context.CompanyById(companyID);

Linq query in Entity Framework multiple children entities include()

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related