Entity Framework - Include Multiple Levels of Properties

Question

The Include() method works quite well for Lists on objects. But what if I need to go two levels deep? For example, the method below will return ApplicationServers with the included properties shown here. However, ApplicationsWithOverrideGroup is another container that holds other complex objects. Can I do an Include() on that property as well? Or how can I get that property to fully load?

As it stands now, this method:

public IEnumerable<ApplicationServer> GetAll()
{
    return this.Database.ApplicationServers
        .Include(x => x.ApplicationsWithOverrideGroup)                
        .Include(x => x.ApplicationWithGroupToForceInstallList)
        .Include(x => x.CustomVariableGroups)                
        .ToList();
}

Will populate only the Enabled property (below) and not the Application or CustomVariableGroup properties (below). How do I make this happen?

public class ApplicationWithOverrideVariableGroup : EntityBase
{
    public bool Enabled { get; set; }
    public Application Application { get; set; }
    public CustomVariableGroup CustomVariableGroup { get; set; }
}

Crowned

Hi, Why I get a exception Expression must be a member expression when I try this: To include a collection and then a collection one level down: query.Include(e => e.Level1Collection.Select(l1 => l1.Level2Collection)). – Bashibazouk

@BobHorn, I have the same issue.. In my case, the nesting goes deep down multiple layers, i managed to do a include you pointed out. In the SQL which got generated, i could see all columns are returning with different alias name as c1,c2 something like that. My question is , how i can form a nested DTO collection out of all my includes:(.. May be you can take the above example itself, in that we are returning all the columns without any custom DTO (which itself is collection of DTO's) – Mascarenas

Answer 1

For EF 6

using System.Data.Entity;

query.Include(x => x.Collection.Select(y => y.Property))

Make sure to add using System.Data.Entity; to get the version of Include that takes in a lambda.

---

For EF Core

Use the new method ThenInclude

using Microsoft.EntityFrameworkCore;

query.Include(x => x.Collection)
     .ThenInclude(x => x.Property);

Dzoba

I can't do Include() on ApplicationsWithOverrideGroup. It doesn't show up in intellisense. – Crowned

I can't use your edit because ApplicationsWithOverrideGroup is a List. Application is a property on each item in the list, not on the list itself. – Crowned

Ahhhh, but that link you provided seems to provide the answer. Let me try this: To include a collection and then a collection one level down: query.Include(e => e.Level1Collection.Select(l1 => l1.Level2Collection)). – Crowned

Remember to include System.Data.Entity in the usings. Otherwise Intellisense will only give you the Include(string path) version of the method. – Naoma

Bob, it would appear from your comments that Equiso's answers above is not quite correct. Do you mind updating the listed code to show the correct working code? – Vertebra

@SirJuice I did update it after his comments, it is working as intended. – Dzoba

What if we have to include more than one property inside a collection and in 3rd case property inside a collection which is inside another collection say Vendors and Customers inside all cities of all states state > City > Customers, Vendors – Fraze

@Fraze you need to call Include for each property: Db.States.Include(state => state.Cities.Select(city => city.Customers).Include(state => state.Cities.Select(city => city.Vendors) – Dzoba

You can do Db.States.Include(s => s.Cities).ThenInclude(c => c.Customers). The trick is that Intellisense won't give you a drop-down on the second include properties, but just forge ahead and type it anyway. It will build and work! It's a bug in intellisense only. NOTE - for EF Core only. – Iapetus

Does EF Core support the EF style nested includes? – Dehaven

For EF Core, you'll need to include using Microsoft.EntityFrameworkCore; to get access to Include and ThenInclude. – Ryswick

I found out using EF Core that the syntax does not autocomplete correctly when using Include on a collection and then ThenInclude on a property of the collection. – Emasculate

For EF Core 3.0+, if you use multiple ThenInclude, the SQL will do a single query to the database, possibly doing a lot of JOINs (behaving almost like a CROSS APPLY) and giving a huge result with repeated results. – Latakia

have been looking for a while on how to load a many to many table without doing a second request. ThenInclude saved the day – Jacky

Answer 2

If I understand you correctly you are asking about including nested properties. If so :

.Include(x => x.ApplicationsWithOverrideGroup.NestedProp)

or

.Include("ApplicationsWithOverrideGroup.NestedProp")  

or

.Include($"{nameof(ApplicationsWithOverrideGroup)}.{nameof(NestedProp)}")  

Exorbitant

Thanks, I can try that. I was hoping to be able to keep things strongly typed and avoid string literals. But if that's how it has to be done... – Crowned

You were close. I may not have been clear that ApplicationsWithOverrideGroup was a list. Thanks for helping! – Crowned

@Judo, I have the same issue.. In my case, the nesting goes deep down multiple layers, i managed to do a include you pointed out. In the SQL which got generated, i could see all columns are returning with different alias name as c1,c2 something like that. My question is , how i can form a nested DTO collection out of all my includes:(.. May be you can take the above example itself, in that we are returning all the columns without any custom DTO (which itself is collection of DTO's) – Mascarenas

Remember to include System.Data.Entity in the usings. Otherwise Intellisense will only give you the Include(string path) version of the method. – Incommunicable

Hello, if for EF core this does indeed work .Include(x => x.ApplicationsWithOverrideGroup.NestedProp) If so what is the point in using Include / ThenInclude as they both work and produce the same SQL. Am i missing something? – Burseraceous

Answer 3

EF Core: Using "ThenInclude" to load mutiple levels: For example:

var blogs = context.Blogs
    .Include(blog => blog.Posts)
        .ThenInclude(post => post.Author)
        .ThenInclude(author => author.Photo)
    .ToList();

Gennagennaro

Looks like this is EF Core only – Willey

FYI: VS2017 the intellisense wasn't working for .ThenInclude. Just type it in how you think it should be and the error highlighting should go away. – Emilioemily

I want to emphasize @Emilioemily 's comment, the Intellisense can sometimes take especially long to handle these ThenInclude , this can be quite confusing for new users. I also had cases where the simple Include lambda expression was not handled properly, until you just type it and compile it, ignoring the "errors" shown in VS. – Bounder

Answer 4

The EFCore examples on MSDN show that you can do some quite complex things with Include and ThenInclude.

This is a good example of how complex you can get (this is all one chained statement!):

viewModel.Instructors = await _context.Instructors

      .Include(i => i.OfficeAssignment)

      .Include(i => i.CourseAssignments)
        .ThenInclude(i => i.Course)
            .ThenInclude(i => i.Enrollments)
                .ThenInclude(i => i.Student)

      .Include(i => i.CourseAssignments)
        .ThenInclude(i => i.Course)
            .ThenInclude(i => i.Department)

      .AsNoTracking()
      .OrderBy(i => i.LastName)
      .ToListAsync();

You can have multiple Include calls - even after ThenInclude and it kind of 'resets' you back to the level of the top level entity (Instructors).

You can even repeat the same 'first level' collection (CourseAssignments) multiple times followed by separate ThenIncludes commands to get to different child entities.

Note your actual query must be tagged onto the end of the Include or ThenIncludes chain. The following does NOT work:

var query = _context.Instructors.AsQueryable();
query.Include(i => i.OfficeAssignment);

var first10Instructors = query.Take(10).ToArray();

Would strongly recommend you set up logging and make sure your queries aren't out of control if you're including more than one or two things. It's important to see how it actually works - and you'll notice each separate 'include' is typically a new query to avoid massive joins returning redundant data.

AsNoTracking can greatly speed things up if you're not intending on actually editing the entities and resaving.

---

EFCore 5 made some changes to the way queries for multiple sets of entities are sent to the server. There are new options for Split Queries which can make certain queries of this type far more efficient with fewer joins, but make sure to understand the limitations - and enable logging to avoid performance surprises later.

Nether

Is there a way to get both the Enrollment and the Departments without your repeated .Includes for CourseAssignment and Course? (So far, it seems like the Api can go deeper with .ThenInclude, or back to the top level with .Include, but there is nothing to stay at the same level?) – Borzoi

If you want lazy-loading stay tuned for EF Core 2.1 blogs.msdn.microsoft.com/dotnet/2018/02/02/… but if you just want to load more at the same level I think this is by design. I'm not sure what you're thinking - it doesn't require much extra to do this and it greatly reduces what comes back from the database. An entity may just have one or two 'same-level' things but it may also have 50 for a large project, being explicit makes your app much faster. – Nether

This was a good explanation of the concept of the Include "resetting" the level back to the initial level again. Helped me wrap my head around the heirarchy of the includ system. Cheers! – Cirro

Answer 5

I made a little helper for Entity Framework 6 (.Net Core style), to include sub-entities in a nice way.

It is on NuGet now : Install-Package ThenInclude.EF6

using System.Data.Entity;

var thenInclude = context.One.Include(x => x.Twoes)
    .ThenInclude(x=> x.Threes)
    .ThenInclude(x=> x.Fours)
    .ThenInclude(x=> x.Fives)
    .ThenInclude(x => x.Sixes)
    .Include(x=> x.Other)
    .ToList();

The package is available on GitHub.

Evangelist

hi, i have an exception at runtime, cannot cast IncludableQueryable<observablecollection> to IncludableQueryable<genericcollection> – Cruise

i am using db first and i have modified the tt file to get ObservableCollections for all my entities, any help is welcome . – Cruise

@lenny32 anything to be aware of with this extension? – Media

Note that this is not required if the property you're navigating to is one-to-one with the DbSet you navigated from, and you can chain DbSet<One>().Include(x => x.Two.Three.Four.Five.Six) with the only drawback being you're computing a cartesian product and potentially increasing bandwidth. – Lola

Answer 6

I also had to use multiple includes and at 3rd level I needed multiple properties

(from e in context.JobCategorySet
                      where e.Id == id &&
                            e.AgencyId == agencyId
                      select e)
                      .Include(x => x.JobCategorySkillDetails)
                      .Include(x => x.Shifts.Select(r => r.Rate).Select(rt => rt.DurationType))
                      .Include(x => x.Shifts.Select(r => r.Rate).Select(rt => rt.RuleType))
                      .Include(x => x.Shifts.Select(r => r.Rate).Select(rt => rt.RateType))
                      .FirstOrDefaultAsync();

This may help someone :)

Dewan

can this be done without repeating .Include(x => x.Shifts.Select(r => r.Rate).Select(rt => rt...... – Sculpsit

well it depends, how deep you wanna go – Dewan

Answer 7

Let me state it clearly that you can use the string overload to include nested levels regardless of the multiplicities of the corresponding relationships, if you don't mind using string literals:

query.Include("Collection.Property")

Dihedral

This method was helpful for me to figure out how this can be coded in VB , as i cant find anywhere after hours of googling. – Manizales

This works great for me, I use this a lot!!! It even works combined with .SelectMany statements : query.SelectMany(x=>x.foos).Include("bar").Include("bar.docs")... – Epilimnion

Answer 8

I'm going to add my solution to my particular problem. I had two collections at the same level I needed to include. The final solution looked like this.

var recipe = _bartendoContext.Recipes
    .Include(r => r.Ingredients)
    .ThenInclude(r => r.Ingredient)
    .Include(r => r.Ingredients)
    .ThenInclude(r => r.MeasurementQuantity)
    .FirstOrDefault(r => r.Id == recipeId);
if (recipe?.Ingredients == null) return 0m;
var abv = recipe.Ingredients.Sum(ingredient => ingredient.Ingredient.AlcoholByVolume * ingredient.MeasurementQuantity.Quantity);
return abv;

This is calculating the percent alcohol by volume of a given drink recipe. As you can see I just included the ingredients collection twice then included the ingredient and quantity onto that.

Dumb

Answer 9

I figured out a simplest way. You don't need to install package ThenInclude.EF or you don't need to use ThenInclude for all nested navigation properties. Just do like as shown below, EF will take care rest for you. example:

var thenInclude = context.One.Include(x => x.Twoes.Threes.Fours.Fives.Sixes)
.Include(x=> x.Other)
.ToList();

Couloir

No. You can't do that with collections. It only works if all properties are references. Your naming suggests the opposite. – Latham

Answer 10

I have an Index page that displays MbsNavigation.Name that is a Firmware object loaded as foreign key. The Firmware object is big, so it takes few minutes to load Index page via an Internet.

BatterySystem = await _context.BatterySystems.Include(b => b.MbsNavigation)

This is a solution to load Firmware.Name only:

BatterySystem = await _context.BatterySystems
.Include(b => b.MbsNavigation)
.Select(b => new BatterySystem() 
{
    Name = b.Name,
    MbsNavigation = new Firmware() { Name = b.MbsNavigation.Name },
})
.ToListAsync();

Now the Index loads immediately.

Experienced