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#.
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.
