Entity Framework Core 2.0.1 Eager Loading on all nested related entities

Question

I have a simple problem, but cant seem to find a way around it. I am using Entity Framework Core version 2.0.1 and want to eager load all my entities by default.

Example:

public class Order
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; } 
    public string Name { get; set; }
    public int AddressId { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string PostCode { get; set; }
    public string City { get; set; }
}

But when I load Order entity the related entity Customer and then inside it Address is null

What i have tried:

• Tried upgrading to version 2.1 and use LazyLoadingProxies set to false

This is just an example, I have entities with multiple nested levels and I want to load nested related data inside of a Generic Repository, so can't use Include and ThenInclude as I don't know the actual entity type when loading it.

Example:

    public virtual async Task<IEnumerable<T>> GetAllAsync(Expression<Func<T, bool>> predicate = null)
    {
        if (predicate == null)
        {
            return await Context.Set<T>().ToListAsync();
        }
        return await Context.Set<T>().Where(predicate).ToListAsync();
    }

What am I missing? Is there something wrong I am doing in the repository? Any help or pointer towards a better design (if that's what the issue is here) are appreciated.

Thanks

Answer 1

Such feature officially does not exist currently (EF Core 2.0.2 and also the incoming 2.1). It's been requested in Eager load all navigation properties #4851(Closed) and currently is tracked by Rule-based eager load (include) #2953 and Allow for declaring aggregates in the model (e.g. defining included properties or by some other means) #1985 (both in Backlog, i.e. no concrete schedule).

I can offer the following two custom extension methods:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore.Metadata;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static IQueryable<T> Include<T>(this IQueryable<T> source, IEnumerable<string> navigationPropertyPaths)
            where T : class
        {
            return navigationPropertyPaths.Aggregate(source, (query, path) => query.Include(path));
        }

        public static IEnumerable<string> GetIncludePaths(this DbContext context, Type clrEntityType, int maxDepth = int.MaxValue)
        {
            if (maxDepth < 0) throw new ArgumentOutOfRangeException(nameof(maxDepth));
            var entityType = context.Model.FindEntityType(clrEntityType);
            var includedNavigations = new HashSet<INavigation>();
            var stack = new Stack<IEnumerator<INavigation>>();
            while (true)
            {
                var entityNavigations = new List<INavigation>();
                if (stack.Count <= maxDepth)
                {
                    foreach (var navigation in entityType.GetNavigations())
                    {
                        if (includedNavigations.Add(navigation))
                            entityNavigations.Add(navigation);
                    }
                }
                if (entityNavigations.Count == 0)
                {
                    if (stack.Count > 0)
                        yield return string.Join(".", stack.Reverse().Select(e => e.Current.Name));
                }
                else
                {
                    foreach (var navigation in entityNavigations)
                    {
                        var inverseNavigation = navigation.FindInverse();
                        if (inverseNavigation != null)
                            includedNavigations.Add(inverseNavigation);
                    }
                    stack.Push(entityNavigations.GetEnumerator());
                }
                while (stack.Count > 0 && !stack.Peek().MoveNext())
                    stack.Pop();
                if (stack.Count == 0) break;
                entityType = stack.Peek().Current.GetTargetType();
            }
        }

    }
}

The first is just a convenient way of applying multiple string base Include.

The second does the actual job of collecting all Include paths for a type using EF Core provided metadata. It's basically directed cyclic graph processing starting with the passed entity type, excluding the inverse navigations of the included paths and emitting only the paths to "leaf" nodes.

The usage in your example could be like this:

public virtual async Task<IEnumerable<T>> GetAllAsync(Expression<Func<T, bool>> predicate = null)
{
    var query = Context.Set<T>()
        .Include(Context.GetIncludePaths(typeof(T));
    if (predicate != null)
        query = query.Where(predicate);
    return await query.ToListAsync();
}

Answer 2

Ivan's answer is fantastic. I've adapted it slightly (using help from Chistoph's code here) just so the extension method can be chained off of the DbContext itself, in case others find that more convenient. For example, in my codebase I can write:

_commissionsContext.CommissionRulesetScopes.IncludeAll().ToListAsync();

And this will eagerly load the entire subgraph of entities for every CommissionRulesetScope:

SELECT [c].[CommissionPlanId], [c].[StartPeriod], [c].[CommissionRulesetId], [c0].[Id], [c0].[Name], [c1].[Id], [c1].[CsiScoreRuleId], [c1].[DealerOptionCommissionRuleId], [c1].[EmailCaptureRuleId], [c1].[ProductCommissionRuleId], [c1].[ProductConsistencyRuleId], [c1].[UnitCommissionRulesetId], [c2].[Id], [c2].[ConsecutiveFailurePenalty], [c2].[CurrentMonthPenalty], [c2].[Enabled], [c2].[Target], [d].[Id], [e].[Id], [e].[Enabled], [e].[Penalty], [e].[Target], [p].[Id], [p0].[Id], [p0].[CommissionBonus], [p0].[Enabled], [p0].[ProductTarget], [p0].[UnitTarget], [u].[Id], [u].[AverageCsiScoreRuleId], [u].[FinancePenetrationRuleId], [u].[GuaranteePeriodCommissionLevel], [u].[MinimumRequiredCsiReturnRate], [u].[MonthlyExpectationAttainmentRuleId], [u].[UnitCommissionTable], [a].[Id], [f].[Id], [m].[Id], [d0].[DealerOptionCommissionRuleId], [d0].[MinimumValue], [d0].[Commission], [t].[ProductCommissionRuleId], [t].[ProductTypeId], [t].[Commission], [t].[Id], [t].[Description], [t].[Key], [t0].[ProductConsistencyRuleId], [t0].[ProductMinMixRangeId], [t0].[Id], [t0].[ProductTypeId], [t0].[Id0], [t0].[Description], [t0].[Key], [t0].[ProductMinMixRangeId0], [t0].[MinimumUnitsTarget], [t0].[Target], [a0].[RuleId], [a0].[Target], [a0].[Points], [f0].[RuleId], [f0].[Target], [f0].[Points], [m0].[RuleId], [m0].[Target], [m0].[Points]
FROM [CommissionRulesetScope] AS [c]
INNER JOIN [CommissionPlan] AS [c0] ON [c].[CommissionPlanId] = [c0].[Id]
INNER JOIN [CommissionRuleset] AS [c1] ON [c].[CommissionRulesetId] = [c1].[Id]
LEFT JOIN [CsiScoreRule] AS [c2] ON [c1].[CsiScoreRuleId] = [c2].[Id]
LEFT JOIN [DealerOptionCommissionRule] AS [d] ON [c1].[DealerOptionCommissionRuleId] = [d].[Id]
LEFT JOIN [EmailCaptureRule] AS [e] ON [c1].[EmailCaptureRuleId] = [e].[Id]
LEFT JOIN [ProductCommissionRule] AS [p] ON [c1].[ProductCommissionRuleId] = [p].[Id]
LEFT JOIN [ProductConsistencyRule] AS [p0] ON [c1].[ProductConsistencyRuleId] = [p0].[Id]
LEFT JOIN [UnitCommissionRuleset] AS [u] ON [c1].[UnitCommissionRulesetId] = [u].[Id]
LEFT JOIN [AverageCsiScoreRule] AS [a] ON [u].[AverageCsiScoreRuleId] = [a].[Id]
LEFT JOIN [FinancePenetrationRule] AS [f] ON [u].[FinancePenetrationRuleId] = [f].[Id]
LEFT JOIN [MonthlyExpectationAttainmentRule] AS [m] ON [u].[MonthlyExpectationAttainmentRuleId] = [m].[Id]
LEFT JOIN [DealerOptionCommission] AS [d0] ON [d].[Id] = [d0].[DealerOptionCommissionRuleId]
LEFT JOIN (
    SELECT [p1].[ProductCommissionRuleId], [p1].[ProductTypeId], [p1].[Commission], [p2].[Id], [p2].[Description], [p2].[Key]
    FROM [ProductCommission] AS [p1]
    LEFT JOIN [ProductType] AS [p2] ON [p1].[ProductTypeId] = [p2].[Id]
) AS [t] ON [p].[Id] = [t].[ProductCommissionRuleId]
LEFT JOIN (
    SELECT [p3].[ProductConsistencyRuleId], [p3].[ProductMinMixRangeId], [p4].[Id], [p4].[ProductTypeId], [p5].[Id] AS [Id0], [p5].[Description], [p5].[Key], [p6].[ProductMinMixRangeId] AS [ProductMinMixRangeId0], [p6].[MinimumUnitsTarget], [p6].[Target]
    FROM [ProductMinMixRangeAssociation] AS [p3]
    INNER JOIN [ProductMinMixRange] AS [p4] ON [p3].[ProductMinMixRangeId] = [p4].[Id]
    INNER JOIN [ProductType] AS [p5] ON [p4].[ProductTypeId] = [p5].[Id]
    LEFT JOIN [ProductMinMixTarget] AS [p6] ON [p4].[Id] = [p6].[ProductMinMixRangeId]
) AS [t0] ON [p0].[Id] = [t0].[ProductConsistencyRuleId]
LEFT JOIN [AverageCsiScoreThreshold] AS [a0] ON [a].[Id] = [a0].[RuleId]
LEFT JOIN [FinancePenetrationThreshold] AS [f0] ON [f].[Id] = [f0].[RuleId]
LEFT JOIN [MonthlyExpectationAttainmentThreshold] AS [m0] ON [m].[Id] = [m0].[RuleId]
ORDER BY [c].[CommissionPlanId], [c].[StartPeriod], [c0].[Id], [c1].[Id], [d0].[DealerOptionCommissionRuleId], [d0].[MinimumValue], [t].[ProductCommissionRuleId], [t].[ProductTypeId], [t0].[ProductConsistencyRuleId], [t0].[ProductMinMixRangeId], [t0].[Id], [t0].[Id0], [t0].[ProductMinMixRangeId0], [t0].[MinimumUnitsTarget], [a0].[RuleId], [a0].[Target], [f0].[RuleId], [f0].[Target], [m0].[RuleId], [m0].[Target]

Here's the adaption:

public static class DbSetExtensions
{
    /// <summary>
    /// Ensures that all navigation properties (up to a certain depth) are eagerly loaded when entities are resolved from this
    /// DbSet.
    /// </summary>
    /// <returns>The queryable representation of this DbSet</returns>
    public static IQueryable<TEntity> IncludeAll<TEntity>(
        this DbSet<TEntity> dbSet,
        int maxDepth = int.MaxValue) where TEntity : class
    {
        IQueryable<TEntity> result = dbSet;
        var context = dbSet.GetService<ICurrentDbContext>().Context;
        var includePaths = GetIncludePaths<TEntity>(context, maxDepth);

        foreach (var includePath in includePaths)
        {
            result = result.Include(includePath);
        }

        return result;
    }

    /// <remarks>
    /// Adapted from https://stackoverflow.com/a/49597502/1636276
    /// </remarks>
    private static IEnumerable<string> GetIncludePaths<T>(DbContext context, int maxDepth = int.MaxValue)
    {
        if (maxDepth < 0)
            throw new ArgumentOutOfRangeException(nameof(maxDepth));

        var entityType = context.Model.FindEntityType(typeof(T));
        var includedNavigations = new HashSet<INavigation>();
        var stack = new Stack<IEnumerator<INavigation>>();

        while (true)
        {
            var entityNavigations = new List<INavigation>();

            if (stack.Count <= maxDepth)
            {
                foreach (var navigation in entityType.GetNavigations())
                {
                    if (includedNavigations.Add(navigation))
                        entityNavigations.Add(navigation);
                }
            }

            if (entityNavigations.Count == 0)
            {
                if (stack.Count > 0)
                    yield return string.Join(".", stack.Reverse().Select(e => e.Current!.Name));
            }
            else
            {
                foreach (var navigation in entityNavigations)
                {
                    var inverseNavigation = navigation.FindInverse();
                    if (inverseNavigation != null)
                        includedNavigations.Add(inverseNavigation);
                }

                stack.Push(entityNavigations.GetEnumerator());
            }

            while (stack.Count > 0 && !stack.Peek().MoveNext())
                stack.Pop();

            if (stack.Count == 0)
                break;

            entityType = stack.Peek().Current!.GetTargetType();
        }
    }
}

Answer 3

Use .Include("Order.Customer.Address");

Supports: .NET Core 3.1.8+ for sure, but I do not know if earlier as well

Answer 4

The current "Ivan Stoev" accepted solution and "Tagc" answer are great. But it didn't work for me because I'm using TPT (Table Per Type) instead of TPH (Table Per Hierarchy).

So I made a little modification in the accepted solution that I share here just to help those who needs it. By the way, I think my solution should works in all cases, either for TPH, but I haven't thoroughly tested it.

I also replaced some Obsolete function usage.

Usage:

using (var ctx = Ctx.Get())
{
  _elecNets.AddRange(ctx.ElectricNetworks.IncludeAll().AsNoTracking());
}

Code:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Diagnostics;

namespace GeneralDb
{
    /// <summary>
    /// From: https://stackoverflow.com/questions/49593482/entity-framework-core-2-0-1-eager-loading-on-all-nested-related-entities
    /// </summary>
    public static partial class EfCoreExtensions
    {
        /* Usage : 
         * 
         * 
         * 
                public virtual async Task<IEnumerable<T>> GetAllAsync(Expression<Func<T, bool>> predicate = null)
                {
                    var query = Context.Set<T>().Include(Context.GetIncludePaths(typeof(T));
                    if (predicate != null)
                        query = query.Where(predicate);

                    return await query.ToListAsync();
                }
         * 
         * 
         */


        public static IQueryable<T> Include<T>(this IQueryable<T> source, IEnumerable<string> navigationPropertyPaths) where T : class
        {
            return navigationPropertyPaths.Aggregate(source, (query, path) => query.Include(path));
        }

        // ******************************************************************
        /// <summary>
        /// Ensures that all navigation properties (up to a certain depth) are eagerly loaded when entities are resolved from this
        /// DbSet.
        /// </summary>
        /// <returns>The queryable representation of this DbSet</returns>
        public static IQueryable<TEntity> IncludeAll<TEntity>(
            this DbSet<TEntity> dbSet,
            int maxDepth = int.MaxValue) where TEntity : class
        {
            IQueryable<TEntity> result = dbSet;
            var context = dbSet.GetService<ICurrentDbContext>().Context;
            var includePaths = GetIncludePaths<TEntity>(context, maxDepth);

            foreach (var includePath in includePaths)
            {
                result = result.Include(includePath);
            }

            return result;
        }

        // ******************************************************************
        /// <remarks>
        /// Adapted from https://stackoverflow.com/a/49597502/1636276
        /// 
        /// EO : Original code only works for TPH not TPT
        /// 
        /// </remarks>
        public static IEnumerable<string> GetIncludePaths<T>(this DbContext context, int maxDepth = int.MaxValue)
        {
            if (maxDepth < 0)
                throw new ArgumentOutOfRangeException(nameof(maxDepth));

            var entityType = context.Model.FindEntityType(typeof(T));
            if (entityType == null)
            {
                throw new ArgumentException($"Unable to find the type: {typeof(T)} in the DbCOntext");
            }

            var includedNavigations = new HashSet<INavigation>();
            var stack = new Stack<IEnumerator<INavigation>>();

            while (true)
            {
                var entityNavigations = new List<INavigation>();
                if (stack.Count <= maxDepth)
                {
                    foreach (INavigation navigation in entityType.GetNavigations())
                    {
                        if (includedNavigations.Add(navigation))
                            entityNavigations.Add(navigation);
                    }

                    // EO: Here for TPT (Table Per Type), we also need to retreive navigations from 
                    // derived class which have a corresponding <DBSet>
                    foreach (var entityTypeDerived in entityType.GetDerivedTypes())
                    {
                        foreach (INavigation navigation in entityTypeDerived.GetNavigations())
                        {
                            if (includedNavigations.Add(navigation))
                                entityNavigations.Add(navigation);
                        }
                    }
                }
                if (entityNavigations.Count == 0)
                {
                    if (stack.Count > 0)
                        yield return string.Join(".", stack.Reverse().Select(e => e.Current.Name));
                }
                else
                {
                    foreach (var navigation in entityNavigations)
                    {
                        var inverseNavigation = navigation.Inverse;
                        if (inverseNavigation != null)
                            includedNavigations.Add(inverseNavigation);
                    }
                    stack.Push(entityNavigations.GetEnumerator());
                }
                while (stack.Count > 0 && !stack.Peek().MoveNext())
                    stack.Pop();
                if (stack.Count == 0) break;
                entityType = stack.Peek().Current.TargetEntityType;
            }
        }

        // ******************************************************************
    }
}