Is there a way, using LINQ / EF, to get the topmost element in the parent / child hierarchy?

Question

I have a class called Structure :

 public class Structure { public int StructureId { get; set; } public Structure Parent { get; set; } } 

As you can see, Structure has a parent Structure . There may be an undetermined number of structures in this hierarchy.

Is there a way using LINQ (with Entity Framework) to get the topmost structure in this hierarchy?

Currently, I have to hit the database several times to find the topmost parent. The oldest parent is Structure with the null Parent property:

 Structure structure = structureRepository.Get(id); while (structure.Parent != null) { structure = structureRepository.Get(structure.Parent.StructureId); } // When we're here; `structure` is now the top most parent. 

So, is there an elegant way to do this with LINQ / Lambdas? Ideally, starting with the following code:

 var structureQuery = from item in context.Structures where item.StructureId == structureId select item; 

I just want to write something like the following so that I delete only one database backend:

 structureQuery = Magic(structureQuery); Structure topMostParent = structureQuery.Single(); 

Answer 1

I think the best I'm going to get is to load the entire hierarchy in one hit from the structure in which I want the top parent:

 var structureQuery = from item in context.Structures .Include(x => x.Parent) where item.StructureId == structureId select item; 

Then just use the code:

 while (structure.Parent != null) { structure = structure.Parent; } 

Answer 2

This is not a direct answer, but the problem you are facing is related to how you store your tree. There are several ways to simplify this query by structuring the data in different ways.

One of them is to use a nested set hierarchy , which can simplify many types of tree queries.

Another is to store a denominated table of the roots of ancestors / descendants / depths. Then this query becomes finding the tuple with the current structure as a descendant with the maximum depth.

Answer 3

I have the same situation. I was not able to solve it directly with LINQ / EF. Instead, I decided to create a database view using recursive generic table expressions, as described here . I created a user-defined function that intersects all parents with a child (or vice versa), and then a view that uses this user-defined function, which I imported into my EF object context.

(disclaimer: simplified code, I really have not tested this)

I have two tables, for example MyTable (containing all the elements) and MyParentChildTable containing the relations ChildId, ParentId

Then I defined the following udf:

 CREATE FUNCTION dbo.fn_getsupertree(@childid AS INT) RETURNS @TREE TABLE ( ChildId INT NOT NULL ,ParentId INT NULL ,Level INT NOT NULL ) AS BEGIN WITH Parent_Tree(ChildId, ParentId) AS ( -- Anchor Member (AM) SELECT ChildId, ParentId, 0 FROM MyParentChildTable WHERE ChildId = @childid UNION all -- Recursive Member (RM) SELECT info.ChildId, info.ParentId, tree.[Level]+1 FROM MyParentChildTable AS info JOIN Parent_Tree AS tree ON info.ChildId = tree.ParentId ) INSERT INTO @TREE SELECT * FROM Parent_Tree; RETURN END 

and the following view:

 CREATE VIEW VwSuperTree AS ( SELECT tree.* FROM MyTable CROSS APPLY fn_getsupertree(MyTable.Id) as tree ) GO 

This gives me for each child all parents with their "tree level" (direct parent has level 1, parent parent has level 2, etc.). From this point of view, it is easy to request the item with the highest level. I just imported the view into my EF context to be able to query it using LINQ.

Answer 4

I like this question and I can’t figure out how to do it. But could you implement this in your repository class? In the end, there should be only one at the top, and if there is a need for it, then perhaps it deserves structureRepository.GetRoot() or something like that.

Answer 5

you can use linq take construct like

  var first3Customers = ( from c in customers select new {c.CustomerID, c.CustomerName} ) .Take(2);