How to include property of table per hierarchy in EF Core eager loading

Question

Consider the following model classes:

public abstract class A
{
    public enum T { B_, C_, D_ }
    public int Id { get; set; }

    public int? ParentId { get; set; }
    public A? Parent { get; set; }

    public virtual ICollection<A> Children { get; set; } = []; // an B has Cs and a C has Ds as children
    public virtual ICollection<E> Es { get; set; } = [];
}

public class B : A
{
}

public class C : A
{
}

public class D : A
{
    public virtual ICollection<F> Fs { get; set; } = [];
}

public class E
{
}

public class F
{
}

My question is: how do I include everything in a single query of B using eager loading and fluent syntax, so basically I want to do something like the following (which fails by throwing an exception in the last ThenInclude):

private IQueryable<B> Bs => _dbContext.Bs
  .Include(b => b.Children)
  .Include(b => b.Es)
    .ThenInclude(c => c.Children)
// how to include c.Es ?
      .ThenInclude(d => (d as D).Fs)
// and how to I include d.Es ?

I've tried casting C.Children using linq's Cast<T>() or filtering with OfType<T>(), but the exception message (which is thrown then) tells me there are only Where, OrderBy and ThenBy available and casting should be done using 'as' operator or explicit casting, non of them work in the shown example.

Edit

Lets say we extend the model classes as following:

public class E
{
    public int Id { get; set; }
    public int GId { get; set; }
    public virtual G G { get; set; }
}
public class G
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

How do I order A.Es by G.Name?

public IQueryable<B> Bs => _dbContext.Bs
    .Include(b => b.Es)
        .ThenInclude(e => e.G)
    .Include(b => b.Es.OrderBy(e => e.G.Name);

Isn't A.Es already included (ordered as in the db table) as Ivan Stoev states in his answer? How do I order A.Es by G.Name? I can't wrap my head around because for using G I have to include it for which I have to navigate to it for which I have to include A.Es an than it is ordered, right?

Answer 1

Generally such recursive model is problematic. But since you have logical constraints which limit the levels (hopefully enforced by your code because relational databases can't), the question as I understand is how to include a common collection at every level and specific collection at specific level.

Including specific TPH (or TPT / TPC) member must be as explained in the docs - either using C# cast or as operator. And in order to get to a specific level, you must restart the include from the root and repeat the path to the level where you want to add new ThenInclude. Basically creating all paths to leaf nodes. Items in the path are included automatically and only once (even though you are repeating them).

The easiest way to visualize the desired includes in your sample model is with string Include overloads:

private IQueryable<B> Bs => _dbContext.Bs
    .Include("Es")
    .Include("Children.Es")
    .Include("Children.Children.Es")
    .Include("Children.Children.Fs");

But of course this is error prone, so the equivalent "type safe" way is:

private IQueryable<B> Bs => _dbContext.Bs
    .Include(b => b.Es)
    .Include(b => b.Children).ThenInclude(c => c.Es)
    .Include(b => b.Children).ThenInclude(c => c.Children).ThenInclude(d => d.Es)
    .Include(b => b.Children).ThenInclude(c => c.Children).ThenInclude(d => ((D)d).Fs);

I intentionally did not put .ThenIncludes indented on new lines in order to better see the equivalence. Of course you can put them indented on new lines if your coding style requires it.