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

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))`. — Joe.wang, Jan 02 '13 at 09:31
@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) — TechQuery, Nov 20 '15 at 21:32

score 947 · Accepted Answer · edited Mar 09 '22 at 17:24

947

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

edited Mar 09 '22 at 17:24

Bob Horn

33,387
34
113
219

answered May 30 '12 at 19:38

Diego

19,494
4
32
46

1

I can't do Include() on ApplicationsWithOverrideGroup. It doesn't show up in intellisense. – Bob Horn May 30 '12 at 19:42
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. – Bob Horn May 30 '12 at 19:45
1

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)). – Bob Horn May 30 '12 at 19:46
71

Remember to include System.Data.Entity in the usings. Otherwise Intellisense will only give you the Include(string path) version of the method. – OJ Raqueño Jul 28 '13 at 03:15
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? – Mar 01 '14 at 11:09
@SirJuice I did update it after his comments, it is working as intended. – Diego Mar 01 '14 at 19:11
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 – Adeem Feb 24 '16 at 08:19
8

@Adeem 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)` – Diego Feb 24 '16 at 15:13
1

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. – Randy Gamage Jul 11 '18 at 04:37
Does EF Core support the EF style nested includes? – melmi Dec 27 '18 at 17:45
More documentation for this here: https://learn.microsoft.com/en-us/ef/core/querying/related-data – K. Shores Jul 16 '19 at 21:30
1

For EF Core, you'll need to include `using Microsoft.EntityFrameworkCore;` to get access to `Include` and `ThenInclude`. – computercarguy Nov 05 '19 at 19:29
1

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. – Nick Peelman Apr 05 '20 at 12:27
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. – Iúri dos Anjos Apr 13 '20 at 19:06
have been looking for a while on how to load a many to many table without doing a second request. ThenInclude saved the day – Cedric Raymond Mar 14 '23 at 12:47

score 94 · Answer 2 · edited May 06 '17 at 14:47

94

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

edited May 06 '17 at 14:47

tchelidze

8,050
1
29
49

answered May 30 '12 at 19:37

Judo

5,167
3
24
34

7

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... – Bob Horn May 30 '12 at 19:42
1

You were close. I may not have been clear that ApplicationsWithOverrideGroup was a list. Thanks for helping! – Bob Horn May 30 '12 at 19:51
@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) – TechQuery Nov 20 '15 at 21:32
3

Remember to include **System.Data.Entity** in the usings. Otherwise Intellisense will only give you the `Include(string path)` version of the method. – AlexMelw May 29 '18 at 10:32
2

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? – Michael Ceber Dec 20 '20 at 15:30

score 84 · Answer 3 · edited Sep 04 '18 at 13:48

84

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

edited Sep 04 '18 at 13:48

Michael Freidgeim

26,542
16
152
170

answered Jul 10 '16 at 03:13

thangcao

1,819
1
13
14

60

Looks like this is EF Core only – Chris Marisic Jul 25 '16 at 20:59
27

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. – JohnWrensby Apr 22 '17 at 20:29
4

I want to emphasize @JohnWrensby '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. – Pac0 Jun 28 '17 at 08:46

Simon_Weaver · Answer 4 · 2021-06-20T20:36:58.980

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.

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?) — William Jockusch, Feb 08 '18 at 23:02
If you want lazy-loading stay tuned for EF Core 2.1 https://blogs.msdn.microsoft.com/dotnet/2018/02/02/entity-framework-core-2-1-roadmap/ 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. — Simon_Weaver, Feb 08 '18 at 23:06
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! — AFM-Horizon, Nov 06 '19 at 14:50

score 34 · Answer 5 · edited Sep 04 '18 at 14:09

34

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.

edited Sep 04 '18 at 14:09

Michael Freidgeim

26,542
16
152
170

answered Mar 17 '17 at 10:51

Lenny32

624
6
15

hi, i have an exception at runtime, cannot cast IncludableQueryable to IncludableQueryable – user2475096 Mar 29 '18 at 18:00
i am using db first and i have modified the tt file to get ObservableCollections for all my entities, any help is welcome . – user2475096 Mar 29 '18 at 18:01
2

@lenny32 anything to be aware of with this extension? – Aaron Hudon Jun 12 '18 at 04:10
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().Include(x => x.Two.Three.Four.Five.Six)` with the only drawback being you're computing a cartesian product and potentially increasing bandwidth. – John Zabroski May 20 '20 at 00:02

score 27 · Answer 6 · answered Jun 17 '17 at 12:56

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

can this be done without repeating `.Include(x => x.Shifts.Select(r => r.Rate).Select(rt => rt......` — highboi, Jan 10 '18 at 20:58

score 10 · Answer 7 · answered Jul 29 '18 at 05:32

10

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

answered Jul 29 '18 at 05:32

mrmashal

1,721
18
21

1

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. – shab May 05 '19 at 20:59
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")...` – Ephie May 27 '20 at 13:32

score 1 · Answer 8 · answered Oct 05 '20 at 21:10

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.

score 0 · Answer 9 · answered Feb 25 '21 at 11:28

0

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

answered Feb 25 '21 at 11:28

Siddarth Tamagond

9
1

3

No. You can't do that with collections. It only works if all properties are references. Your naming suggests the opposite. – Gert Arnold Feb 25 '21 at 20:45

score 0 · Answer 10 · answered May 17 '22 at 10:14

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.

Entity Framework - Include Multiple Levels of Properties

10 Answers10

Linked

Related