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?

What is the exception? Note that there's no navigation from B to F, so I'm not sure what you're trying to achieve. — Gert Arnold
– Gert Arnold, Commented May 25, 2024 at 9:36
@GertArnold I've currently no access to the exception and its message, I'll post it here later as a comment. You are right that there is no direct navigation from B to F, but since B and D are both of type A I thought it must be somehow possible to include D.Fs in a query of _dbContext.Bs, which is what I'm trying to achive. — dude
– dude, Commented May 25, 2024 at 11:35
I've missed to make clear that, in the db table for A, the children of a B are of type C and the children of a C are of Type D. — dude
– dude, Commented May 25, 2024 at 12:14
Shoudn't in your example Bs to be As? e.g. _dbContext.As.Include(...) ? — Ivan Stoev
– Ivan Stoev, Commented May 25, 2024 at 14:55
But anyway, even without TPH, your model is hierarchical with unlimited (from relational model standpoint) levels, so there is no way to eager load all levels (there is no recursive query support in LINQ to queryable, thus EF Core). — Ivan Stoev
– Ivan Stoev, Commented May 25, 2024 at 15:03

Ivan Stoev · Accepted Answer · 2024-05-25 17:40:24Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related