Entity Framework Core 2.0.1 Eager Loading on all nested related entities
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();
}
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;
}
}
// ******************************************************************
}
}
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();
}
}
}
Use .Include("Order.Customer.Address");
Supports: .NET Core 3.1.8+ for sure, but I do not know if earlier as well