CODEWITHSUNDEEP
c#entity-frameworkeager-loading
Bob Horn
Bob Horn

Reputation: 34325

Entity Framework - Include Multiple Levels of Properties

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

Upvotes: 534

Views: 419856

Answers (10)

Marek Pio
Marek Pio

Reputation: 127

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.

Upvotes: 0

Diego
Diego

Reputation: 20194

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

Upvotes: 992

Simon_Weaver
Simon_Weaver

Reputation: 146180

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.

Upvotes: 63

Siddarth Tamagond
Siddarth Tamagond

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

Upvotes: 0

Darth Scitus
Darth Scitus

Reputation: 568

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.

Upvotes: 1

Lenny32
Lenny32

Reputation: 634

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.

Upvotes: 34

thangcao
thangcao

Reputation: 1899

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

Upvotes: 88

mrmashal
mrmashal

Reputation: 1883

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

Upvotes: 10

dnxit
dnxit

Reputation: 7350

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

Upvotes: 28

Judo
Judo

Reputation: 5247

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

Upvotes: 100

Related Questions