Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...