Entity framework linq query Include() multiple children entities

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

score 207 · Accepted Answer · edited Oct 22 '12 at 17:03

207

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

edited Oct 22 '12 at 17:03

John Leidegren

56,169
16
118
148

answered Jul 29 '10 at 18:52

Nix

52,320
25
137
193

But I would like to using it this like: `//inside public static class Extensions public static IQueryable CompleteCompanies(this DbSet table){ return table .Include("Employee.Employee_Car") .Include("Employee.Employee_Country") ; } //code will be... Company company = context.Companies.CompleteCompanies().FirstOrDefault(c => c.Id == companyID); //same for next advanced method` – Hamid Mar 28 '16 at 08:28
Bullsye Nix. Extensions should be the first port of call for ... well ... extending predefined functionality. – ComeIn Apr 23 '16 at 10:29
14

Years later, I would not recommend the string-based includes, because they aren't runtime safe. If the navigation property name ever changes or is misspelled, it will break. Strongly suggest using the typed include instead. – Jeff Putz Nov 23 '16 at 20:18
2

since the introduction of nameof(class) it is possible to use this approach safely. In case the entity name changes, it will be picked up during compile. Example: context.Companies.Include(nameof(Employee)) In case one needs to go further down names have to concatent with nameof(Employee)+"."+nameof(Employee_Car) – Karl Jan 19 '18 at 09:18
The extension method technique doesn't work for compiled queries (at least not on EFCore) confirmed here: https://github.com/aspnet/EntityFrameworkCore/issues/7016 – Dunge Aug 06 '19 at 14:17

StuartLC · Answer 2 · 2021-01-02T09:32:43.873

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

As per above (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.
As per the docs, I would keep the extra 'indent' in the .ThenInclude to preserve your sanity.

I was wondering how to do it with strongly typed .Include statements. Projecting the children with Select was the answer ! — , May 13 '15 at 13:19
My equiv of "co.Employees.Select(...)" shows a syntax error on "Select", saying that "'Employees' does not contain a definition for 'Select' [or extension method]". I've included System.Data.Entity. I only want to get a single column from the joined table. — Chris Walsh, Jan 17 '17 at 09:55
I had a parent table that was referencing the same child table twice. With the old string include syntax it was difficult to preload the right relationship. This way is a lot more specific. Please keep in mind to include the namespace System.Data.Entity for strongly typed include. — Karl, Jan 19 '18 at 06:26
With .net core 2.1 I needed the namespace Microsoft.EntityFrameworkCore instead of System.Data.Entity — denvercoder9, Oct 04 '18 at 20:56

score 27 · Answer 3 · answered Aug 30 '11 at 07:05

27

You might find this article of interest which is available at codeplex.com.

Improving Entity Framework Query Performance Using Graph-Based Querying.

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.

answered Aug 30 '11 at 07:05

Merijn

573
5
16

how can this be implemented in a real-world application? – Victor.Uduak Apr 05 '19 at 03:38

score 4 · Answer 4 · edited May 23 '17 at 12:18

4

How do you construct a LINQ to Entities query to load child objects directly, instead of calling a Reference property or Load()

There is no other way - except implementing lazy loading.

Or manual loading....

myobj = context.MyObjects.First();
myobj.ChildA.Load();
myobj.ChildB.Load();
...

edited May 23 '17 at 12:18

Community

1
1

answered Jul 28 '10 at 19:20

Andreas Rehm

2,066
14
17

score 1 · Answer 5 · answered Jul 20 '20 at 12:46

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

Entity framework linq query Include() multiple children entities

5 Answers5

Linked