score:2
I think the best I'm going to get is to load the entire hierarchy in one hit from the structure I want the top parent of:
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;
}
score:-1
I like the question and can't think of a linq-y way of doing this. But could you perhaps implement this on your repository class? After all, there should be only one at the top and if the need for it is there, then maybe it deserves a structureRepository.GetRoot()
or something.
score:-4
you can use the linq take construct, for instance
var first3Customers = (
from c in customers
select new {c.CustomerID, c.CustomerName} )
.Take(2);
score:0
I have a similar situation. I didn't manage to solve it directly with LINQ/EF. Instead I solved by creating a database view using recursive common table expressions, as outlined here. I made a user-defined function that cross applies all parents to a child (or vice versa), then a view that makes use of this user-defined function which I imported into my EF object context.
(disclaimer: simplified code, I didn't actually test this)
I have two tables, say MyTable (containing all items) and MyParentChildTable containing the ChildId,ParentId relation
I have then 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 of parent has level 2, etc.). From that view, it's easy to query the item with the highest level. I just imported the view in my EF context to be able to query it with LINQ.
score:2
This is not a direct answer, but the problem you are having is related to the way you are storing your tree. There are a couple ways of simplifying this query by structuring data differently.
One is to use a Nested Set Hierarchy, which can simplify many kinds of queries across trees.
Another is to store a denomralized table of Ancestor/Descendant/Depth tuples. This query then becomes finding the tuple with the current structure as the descendant with the maximum depth.
Source: stackoverflow.com
Related Articles
- Find child objects in list of parent objects using LINQ
- How to return both parent and child using LINQ against 1 table
- Remove child list item when checking grandchild list using Linq
- Is there a way, using LINQ/EF, to get the top most item in a parent/child hierarchy?
- Using LINQ to get one entity with the most of a child entity
- get parent name and child count from model using LINQ
- Linq query to orderby level then parent and child item
- Filter Parent Collection Using Criteria in Child Collection via a Linq Query
- How can I fetch child entities as DTO in parent using reusable queries/Expression's with EF code first?
- Grouping a list of objects by parent using linq to output child elements
- most efficient Entity Framework Code First method of flattening / projecting parent entity with specific child
- Using Lambda / Linq Filter Parent Collection based on Child Items
- How to traverse parent child data using Linq
- How to Filter Child Collection from each Parent Entity in the Parent Collection using Linq
- Refer to a Child item using LINQ inline SELECT
- Using LINQ to switch the parent key with the child keys of a two dimensional dictionary
- How could I create single list from Parent and child node from XML using Linq
- How to render related items in a child ListView using the item ID from the parent
- Reading the last child item in a hierarchy using Linq
- Using LINQ on local squence to group by parent and child properties
- Get child item using linq
- Create a tree structure in linq with a single list source with parent - child as strings of an object
- Linq query to return parent or most recent child
- How select hierarchy from child to parent using linq for entities or EF Core 2?
- How to find recursive Parent Child hierarchy Dynamically using Linq in Mvc
- Using LINQ To Select Records Based on Child Collections item
- How do you populate a parent child data structure from XML where generic List<> objects are in parent and child structures using one LINQ query?
- Linq how to query a list of items for a combined list of a child collection based on a property of the parent item
- How do I Count the number of parent groups using LINQ and child repo
- Retrieve data from child table using parent table LINQ query
- Find matching IDs between two different List Objects only when all IDs or more are present?
- Find closest and smaller value in a list in C# with linq?
- Grouping and multiple orderings using Linq to Entities
- How to filter this specific list?
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- How do I find 10 elements by longitude and latitude in 1 km radius using LINQ?
- How can I generate all permutations / combinations of multiple arrays?
- retrieve the most common element based on the value of a column with C# generics
- LINQ / EF - Using GroupBy to return values to a View
- Efficient way to call .Sum() on multiple properties
- LINQ query syntax with multiple statements
- Navigational Property not materialized in Linq Query Syntax also Lazy Loading didnt work
- CROSS JOIN in DB2 v8 for z/OS
- Dynamic variable in LINQ
- Is it possible to remove a property or at-least set it null from object in a query without changing its type?
- Quick linq count question
- How to extract individual/child nodes from a KML file in VisualBasic?
- Does anyone know of anyone working on a LINQ-to-Memcached provider?
- How to sort xDocument node by name
- Comparison between select then filtering and select distinct in linq, c#