linq - How to retrieve parent child relationship data using entity framework and do pagination/filtering/sorting on it

Question

I have one DB table which is self-referenced to form the parent child relationship.

Id	Name	ParentId
1	A	null
2	B	1
3	C	2
4	D	2
5	E	1

question from:https://stackoverflow.com/questions/65858450/how-to-retrieve-parent-child-relationship-data-using-entity-framework-and-do-pag

Answer 1

It's a common problem - and not something EFCore handles natively.

Point of caution - you almost certainly want to add checking to insure all new /edited records don't inadvertently create circular references, eg: Temp1 = new Temp {Id = 1, ParentId = 2}; Temp2 = new Temp {Id = 2, ParentId = 1}; - this'll cause bad things to happen when loading a tree....

First, the not-so-obvious obvious solution, use lazy-loading after only retrieving the root nodes:

// Get a list of root nodes.
// Optionally, add pagination, filtering and sorting to the query - .Where(x => x.Name.Contains("blah")).Skip(10).Take(5).OrderBy(x => x.Sequence) etc.
var rootNodes = dbContext.Temp.Where(x => x.parentId == null).ToList();

// Child nodes will be loaded as you access them.
foreach (var node in rootNodes)
{
    foreach (var child in node.Children) {
        // Do something with children that were lazy-loaded.
    }
}

For more info on configuring Lazy-Loading: https://csharp.christiannagel.com/2019/01/30/lazyloading/

Note: Lazy Loading CAN lead to inefficient behavior / unexpected DB queries. It's convenient, but convenience comes at a price...

If your business rules dictate a maximum depth, you can use include statements as follows, notice the filter to only retrieve root nodes:

// For a maximum depth of two (or root, leaf, leaf)
// Optionally, add pagination, filtering and sorting to the query - .Where(x => x.Name.Contains("blah")).Skip(10).Take(5).OrderBy(x => x.Sequence) etc.
var query = dbContext.Temp
    .Where(x => x.ParentId == null)
    .Include(x => x.children)
        .ThenInclude(x => x.children);

For trees of unknown depth, you'll best be served by getting the entire flat list and building the nesting yourself (client side instead of EF Query), note that pagination will have to be on the result:

// My recursive function to load child nodes.
Action<List<Temp>, <Temp>> LoadChildren = delegate(sourceList, loadChildrenFor) {
    // Get the children for the specified node.
    loadChildrenFor.Children = sourceList.Where(x => x.ParentId == loadChildrenFor.Id).ToList();
    // Remove the children from the source list AND load its children.
    foreach(var node in loadChildrenFor.Children)
    {
        sourceList.Remove(node); // Don't need in source list any more, it's a child.
        LoadChildren(sourceList, node);
    }
}

var allNodes = dbContext.Temp.ToList();
foreach (var node in allNodes.Where(x => x.parentId == null))
    LoadChildren(allNodes, node);

// Now allNodes ONLY contains root nodes with all their children populated, // Optionally, add pagination, filtering and sorting to the query - .Where(x => x.Name.Contains("blah")).Skip(10).Take(5).OrderBy(x => x.Sequence) etc.

For a generic implementation of list flattening: https://habr.com/en/post/516596/

If the tree is very large and is of unknown depth, and you only need a subset (or one branch) multiple queries inside a recursive function would be most appropriate:

// My recursive function to load child nodes.
Action<Temp> LoadChildren = delegate(ofNode) {
    ofNode.Children = dbContext.Temp.Where(x => x.ParentId == ofNode.Id).ToList();
    foreach(var node in ofNode.Children)
        LoadChildren(node);
}

// A list of root nodes I'm interested in - could be any depth in tree.
// Optionally, add pagination and sorting to the query - .Skip(10).Take(5).OrderBy(x => x.Sequence) etc.
var rootNodesImInterestedIn = dbContext.Temp.Where(x => x.Id == 5).ToList();
// Load the children for each root node I'm interested in.
foreach(var node in rootNodesImInterestedIn)
    LoadChildren(node);