How can I load all descendant items of any particular item, or a list of items?

Question

I have this model of a project:

public class Project
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public Member ProjectOwner { get; set; }
    public List<Project> ChildProjects { get; set; }
    // ... more properties
}

I want to load all child projects and their ProjectOwner for all projects. In my setup, there is no set limit for how long a tail of descendants can be until we get to the "leaf".

At the moment, I'm doing this for the Index-view (yes, it's terrible!):

List<Project> projects = await db.Projects
    .Where(p => p.ParentId == null)
    .Include(o => o.ProjectOwner)

    .Include(c => c.ChildProjects)
        .ThenInclude(o => o.ProjectOwner)

    .Include(c => c.ChildProjects)
        .ThenInclude(c => c.ChildProjects)
            .ThenInclude(o => o.ProjectOwner)

    .Include(c => c.ChildProjects)
        .ThenInclude(c => c.ChildProjects)
            .ThenInclude(c => c.ChildProjects)
                .ThenInclude(c => c.ProjectOwner)

    .Include(c => c.ChildProjects)
        .ThenInclude(c => c.ChildProjects)
            .ThenInclude(c => c.ChildProjects)
                .ThenInclude(c => c.ChildProjects)
                    .ThenInclude(c => c.ProjectOwner)

    .Include(c => c.ChildProjects)
        .ThenInclude(c => c.ChildProjects)
            .ThenInclude(c => c.ChildProjects)
                .ThenInclude(c => c.ChildProjects)
                    .ThenInclude(c => c.ChildProjects)
                        .ThenInclude(c => c.ProjectOwner)

    // ... and so on ... (!)


    .ToListAsync();

How can I load everything in a more effective and dynamic way? The above code is limitied to a fixed number of levels. I can't have that.

I asked a similar question a few days ago, but was not able to make anything out of the replies.

For **all** projects it's quite easy - see https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity/46161259#46161259. Basically `var rootProjects = db.Projects.Include(p => p.ProjectOwner).Include(p => p.ChildProjects).AsEnumerable().Where(p => p.ParentId == null).ToList();` — Ivan Stoev, Oct 19 '19 at 15:49
Explicit loading can definitely solve your issue with n Includes. But, it comes at a price. If you have 20 leaves in this tree, you will get 20 some SQL queries. It's not recommendable for large applications. You could try and solve that issue by using what is called `Fixing Up` where the data retrieved by a database context object is cached and used to populate the navigation properties of objects that are created for subsequent queries. But fixing up is tricky and would require a trial-and-error approach by reviewing SQL queries send to the database. — Dennis VW, Oct 19 '19 at 16:29
I answered a similar question [here](https://stackoverflow.com/a/58401119/3214387) — Shahafo, Oct 19 '19 at 17:36
@IvanStoev Great! That seems to work for a list of projects. But how about just one? — Stian, Oct 19 '19 at 17:47
Unfortunately there is no simple/good/efficient solution for filtered set due to the lack of recursive query support in LINQ/EF Core. — Ivan Stoev, Oct 19 '19 at 22:30
@IvanStoev I did this for one project: var rootProjects = db.Projects.Include(p => p.ProjectOwner).Include(p => p.ChildProjects).AsEnumerable().Where(p => p.Id == id).ToList(); var project = projects.FirstOrDefault(); — Stian, Oct 20 '19 at 08:10
Possible duplicate of [Map category parent id self referencing table structure to EF Core entity](https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity) — LukaszBalazy, Oct 20 '19 at 08:27
@Stian That works of course, but I didn't mention it because it's not efficient - it loads ALL items before performing the filtering in memory. As workaround for small tables - ok, but not as general solution. — Ivan Stoev, Oct 20 '19 at 09:05
@IvanStoev Not all items, only ones with `Id == id`, wich is just one. — Stian, Oct 20 '19 at 09:26

Stian · Answer 1 · 2019-10-20T09:43:00.490

Ivan Stoev gave me this soulution in the comments, for all root items:

var rootProjects = db.Projects
    .Include(p => p.ProjectOwner)
    .Include(p => p.ChildProjects)
    .AsEnumerable()
    .Where(p => p.ParentId == null).ToList();

Since there is no such elegant Linq solution for a filtered set, I just did this for a single item:

Project project = await Task.FromResult(db.Projects
    .Include(p => p.ProjectOwner)
    .Include(p => p.ChildProjects)
        .ThenInclude(o => o.ProjectOwner)
    .AsEnumerable().Where(p => p.Id == id)
    .ToList().FirstOrDefault());

score 0 · Answer 2 · answered Feb 16 '21 at 16:22

I have the same question and I have decided making the next proof of concept:

Make a helper class for working with a tree like: FlatToTree, TreeToFlat, GetAllDescendants, GetAllAncestors and so on
Select from a whole table only two fields ID and ParentID with AsNotracking() option.
In ram via the helper class GetAllDescendants or GetAllAncestors IDs
Then select from the table only items by IDs selected in step 3 with tracking option as var data = _db.Table.Where(r = idList.contains(r.id))
And return data.Where(d => d.id == requestedId)

Sorry for my English.

How can I load all descendant items of any particular item, or a list of items?

2 Answers2