How to eager load and include the children of an ICollection only using include?

Question

The issue I am having involves virtual ICollections of collections not being considered as properties when using include, while still being able to be accessed as such at runtime.

For example LoanClass:

public class Loan : BaseEntity
{
    private ICollection<LoanStatus> _LoanStatuses;

    public virtual ICollection<LoanStatus> LoanStatuses
    {
        get { return _LoanStatuses ?? (_LoanStatuses = new Collection<LoanStatus>()); }
        set { _LoanStatuses = value; }
    }
}

public class LoanStatus : BaseEntity
{
    public int ID { get; set; }
    public int LoanID { get; set; }
    public int StatusID { get; set; }

    [ForeignKey("LoanID")]
    public virtual Loan Loan { get; set; }

    [ForeignKey("StatusID")]
    public virtual CodeType Status { get; set; }
}

public class CodeType : BaseEntity
{
    public int ID { get; set; }
    public string Code { get; set; }
    public string Description { get; set; }
    public string Category { get; set; }
    public int? Sequence { get; set; }

    public bool IsCodeType(CodeTypeCategory category, string code)
    {
        return Category.Equals(category.ToString()) && Code.Equals(code);
    }
}

service layer:

Loan loan = LoanUnitOfWork.LoanRepository.LoadByLoanNumberWithRequiredData(loanNumber,l => l.LoanStatuses);

Repository:

public Loan LoadByLoanNumberWithRequiredData(string loanNumber, params Expression<Func<Loan, object>>[] includes)
{
    return includes.Aggregate(Context.Loans.AsQueryable()
        ,(current, include) => current.Include(include)).FirstOrDefault(l => l.LoanNumber == loanNumber);
}

The issue here is that I can eager load other properties of the loan class because they directly refer to the loan table in the db.

But when I go to eager, load properties on the LoanStatuses table/object like so:

Loan loan = LoanUnitOfWork.LoanRepository.LoadByLoanNumberWithRequiredData(loanNumber,l => l.LoanStatuses.*Status.Code*);

It will not allow me.

And if I leave the italicised code above out of the include query, then the properties are not eager loaded and EF makes many calls out to collect these Status.Codes:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (20ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='600']
SELECT [c].[Ct_ID], [c].[Active], [c].[Ct_Category], [c].[Ct_Code], [c].[Ct_CreatedBy], [c].[Ct_DateCreated], [c].[Ct_DateUpdated], [c].[Ct_Description], [c].[Ct_Sequence], [c].[Ct_UpdatedBy]
FROM [Code_Types] AS [c]
WHERE ([c].[Active] = CAST(1 AS bit)) AND ([c].[Ct_ID] = @__p_0)
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (11ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='600']
SELECT [c].[Ct_ID], [c].[Active], [c].[Ct_Category], [c].[Ct_Code], [c].[Ct_CreatedBy], [c].[Ct_DateCreated], [c].[Ct_DateUpdated], [c].[Ct_Description], [c].[Ct_Sequence], [c].[Ct_UpdatedBy]
FROM [Code_Types] AS [c]
WHERE ([c].[Active] = CAST(1 AS bit)) AND ([c].[Ct_ID] = @__p_0)
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (10ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='600']
SELECT [c].[Ct_ID], [c].[Active], [c].[Ct_Category], [c].[Ct_Code], [c].[Ct_CreatedBy], [c].[Ct_DateCreated], [c].[Ct_DateUpdated], [c].[Ct_Description], [c].[Ct_Sequence], [c].[Ct_UpdatedBy]
FROM [Code_Types] AS [c]
WHERE ([c].[Active] = CAST(1 AS bit)) AND ([c].[Ct_ID] = @__p_0)
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (312ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='600']
SELECT [c].[Ct_ID], [c].[Active], [c].[Ct_Category], [c].[Ct_Code], [c].[Ct_CreatedBy], [c].[Ct_DateCreated], [c].[Ct_DateUpdated], [c].[Ct_Description], [c].[Ct_Sequence], [c].[Ct_UpdatedBy]
FROM [Code_Types] AS [c]
WHERE ([c].[Active] = CAST(1 AS bit)) AND ([c].[Ct_ID] = @__p_0)

How can I eager load the child properties of Loan statuses?

Answer 1

Well I don't think you can. In EF6 you could Include the children of an ICollection using ICollection.Select(), which is still valid Syntax in EF Core, but you get an InvalidOperationException:

InvalidOperationException: The expression 'l.LoanStatuses.AsQueryable().Select(ls=> ls.Status)' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

I had the same Problem, but I found a Solution, that is not exactly what you wanted, but gets the Job done.

Instead of params Expression<Func<Loan, object>>[] includes I used Func<IQueryable<TEntity>, IIncludableQueryable<TEntity, object>> include which would look something like that:

public Loan LoadByLoanNumberWithRequiredData(string loanNumber, Func<IQueryable<Loan>, IIncludableQueryable<Loan, object>> include)
{
    return include(Context.Loans.AsQueryable())
        .FirstOrDefault(l => l.LoanNumber == loanNumber);
}

Loan loan = LoanUnitOfWork.LoanRepository.LoadByLoanNumberWithRequiredData(loanNumber, loan => loan
    .Include(l => l.LoanStatuses).ThenInclude(ls => ls.Status));

I know it's not what you asked for, but it's a neat workaround.