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