CODEWITHSUNDEEP
c#linqrecursion
USER_8675309
USER_8675309

Reputation: 893

Recursively get all nodes that match in LINQ

Is there a way to do the following in linq?

//item properties
//EntityId
//ParentEntityId
//HierarchyLevel

var topItem = myList.Single(x => x.HierarchyLevel == 0);
var level1 = myList.Where(x => x.ParentEntityId == topItem.EntityId);

//can i do the next step in LINQ?
//var level2 =

What about level 3? is I've done this in SQL with CTEs but not sure the right way to do it in C#.

Upvotes: 0

Views: 335

Answers (1)

Ocelot20
Ocelot20

Reputation: 10800

You have a few options:

1) Query for the next level using info from the previous one. So basically using recursion, but not doing everything in one step in LINQ.

var currLevel = myList.Where(x => x.HierarchyLevel == 0);
while (currLevel.Any())
{
    // Note: Maybe faster if myList was converted to a lookup first
    //       for this part, but this won't be an option if this is
    //       Linq2Sql, EF, etc. unless you pull everything into memory.
    var nextLevel = myList.Where(x => currLevel.Select(c => c.EntityId)
                                               .Contains(x.ParentEntityId));

    // Do something here with additional levels.

    var currLevel = nextLevel;
}

2) Use something like LINQ AsHierarchy, which essentially does the same, but presents it in a reusable data structure:

var root = myList.AsHierarchy(x => x.EntityId, x => x.ParentEntityId)
                 .Single();

var level2 = root.ChildNodes;

var level3 = level2.SelectMany(x => x.ChildNodes);

3) Write it as a SQL Function/Stored Proc that has a CTE, then map it to your EF context (assuming you're using EF since you mentioned SQL).

If you're looking for something like LINQ that will convert to a recursive SQL call, there is no such solution. The best you'll get is pulling all the data into memory and doing recursion there, or executing a query once per level.

Upvotes: 1

Related Questions