Entity framework linq query Include() multiple children entities

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

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

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.

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.

https://stackoverflow.com/questions/315966/how-do-you-construct-a-linq-to-entities-query-to-load-child-objects-directly-ins

There is no other way - except implementing lazy loading.

Or manual loading....

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

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

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