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

Receptor

Answer 1

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

Ripon

But I would like to using it this like: //inside public static class Extensions public static IQueryable<Company> CompleteCompanies(this DbSet<Company> 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 – Dichasium

Bullsye Nix. Extensions should be the first port of call for ... well ... extending predefined functionality. – Moonlit

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. – Verticillate

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

The extension method technique doesn't work for compiled queries (at least not on EFCore) confirmed here: github.com/aspnet/EntityFrameworkCore/issues/7016 – Tolerate

Can we add conditions to Include? for example, Include("TableName", conditions) to load specific items in Include? I have an IsDeleted flag in my tables and I don't want to load them in Include. – Rossanarosse

Answer 2

EF Core

For eager loading relationships more than one navigation away (e.g. grand child or grand parent relations), where the intermediate relation is a collection (i.e. 1 to many with the original 'subject'), EF Core has a new extension method, .ThenInclude(), and the syntax is slightly different to the older EF 4-6 syntax:

using Microsoft.EntityFrameworkCore;
...

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.
• Personally, I would keep the extra 'indent' in the .ThenInclude to preserve your sanity.

For serialization of intermediaries which are 1:1 (or N:1) with the original subject, the dot syntax is also supported, e.g.

var company = context.Companies
                     .Include(co => co.City.Country);

This is functionally equivalent to:

var company = context.Companies
                     .Include(co => co.City)
                          .ThenInclude(ci => ci.Country);

However, in EFCore, the old EF4 / 6 syntax of using 'Select' to chain through an intermediary which is 1:N with the subject is not supported, i.e.

var company = context.Companies
                     .Include(co => co.Employee.Select(emp => emp.Address));

Will typically result in obscure errors like

Serialization and deserialization of 'System.IntPtr' instances are not supported

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

Deadening

I was wondering how to do it with strongly typed .Include statements. Projecting the children with Select was the answer ! – Vollmer

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. – Saucer

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. – Eparch

With .net core 2.1 I needed the namespace Microsoft.EntityFrameworkCore instead of System.Data.Entity – Apologize

Answer 3

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.

Temuco

how can this be implemented in a real-world application? – Periderm

@Periderm Indeed. While it's an interesting approach, it's irrelevant to the question, and is far from being something I can just drop into my EF application – Coan

@Victor.Udua, Auspex my answer to the question is more than 10 years old. At that time, complex queries involving multiple children entities were difficult because not strongly typed and the performance was poor. I referred to an alternative approach (GraphBasedQuerying). In the referenced article I showed how to use strongly typed queries and I made an extensive performance comparison. The SW was available on CodePlex back then. There was a follow-up project on github.com/Omar007/GraphBasedQuerying, which you can use. I hope you realize that 11 years ago my answer was relevant :-) – Temuco

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

Envelope

This is not the question that was asked. – Coan

Answer 5

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

Boxfish

Answer 6

To doing this:

namespace Application.Test
{
    using Utils.Extensions;
    public class Test
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Room> Rooms { get; set; }
        public DbSet<Post> Posts { get; set; }
        public DbSet<Comment> Comments { get; set; }
        
        public void Foo()
        {
            DB.Users.Include(x => x.Posts, x => x.Rooms, x => x.Members);
            //OR
            DB.Users.Include(x => x.Posts, x => x.Rooms, x => x.Members)
                .ThenInclude(x => x.Posts, y => y.Owner, y => y.Comments);
        }
    }
}

this extension might be helpful:

namespace Utils.Extensions
{
    using Microsoft.EntityFrameworkCore;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    public static partial class LinqExtension
    {
        public static IQueryable<TEntity> Include<TEntity>(
            this IQueryable<TEntity> sources,
            params Expression<Func<TEntity, object>>[] properties)
            where TEntity : class
        {
            System.Text.RegularExpressions.Regex regex = new(@"^\w+[.]");
            IQueryable<TEntity> _sources = sources;
            foreach (var property in properties)
                _sources = _sources.Include($"{regex.Replace(property.Body.ToString(), "")}");
            return _sources;
        }

        public static IQueryable<TEntity> ThenInclude<TEntity, TProperty>(
            this IQueryable<TEntity> sources,
            Expression<Func<TEntity, IEnumerable<TProperty>>> predicate,
            params Expression<Func<TProperty, object>>[] properties)
            where TEntity : class
        {
            System.Text.RegularExpressions.Regex regex = new(@"^\w+[.]");
            IQueryable<TEntity> _sources = sources;
            foreach (var property in properties)
                _sources = _sources.Include($"{regex.Replace(predicate.Body.ToString(), "")}.{regex.Replace(property.Body.ToString(), "")}");
            return _sources;
        }
    }
}

Smiley