HierarchyID: Get all descendants for a list of parents using LINQ-to-SQL

Question

I need to find all descendants for a given list of parents. The query (in plain SQL) was easy enough, but I can't translate it into LINQ-to-SQL.

SQL Query:

select child.id from GlobalDocumentClassification child inner join GlobalDocumentClassification parent on child.HierarchyId.IsDescendantOf(parent.HierarchyId) = CAST(1 AS bit) and parent.Id in ('5bb9450f-3778-411d-bbf2-5a5775c70310')

Here my (flawed) LINQ query:

var entityIds = from child in Context.GlobalDocumentClassification join parent in Context.GlobalDocumentClassification on child.HierarchyId.IsDescendantOf(child.HierarchyId) equals true where EF.Constant(documentClassificationIds).Contains(parent.Id) select child.Id;

The IDE complains about the use of parent - I believe this is related to how the LINQ-to-SQL queries need to be structured 🤔.

This SO article describes the SQL side of the problem a bit more detailed, but again I do need to express my query with LINQ. Linq to SQL: How to join on no field i.e. cartesian join

Your help is much appreciated

One of these questions might be relevant. The answer likely depends on your ORM. I suggest that you add the relevant ORM and DBMS tags. — T N
– T N, Commented Nov 18, 2024 at 22:33
The LINQ join..on..equals clause doesn't have access to both enumerators simultaneously, it rather computes a key for each collection before independent of one another. What you want is a second from clause followed by a where. Ie from parent in Context.GlobalDocumentClassification where child.HierarchyId.IsDescendantOf(child.HierarchyId). Remember LINQ is not SQL and the worst way of learning it is trying transliterate an existing SQL query — Aluan Haddad
– Aluan Haddad, Commented Nov 18, 2024 at 22:49
I think this can also be formulated as from child in Context.GlobalDocumentClassification where Context.GlobalDocumentClassification.Any(parent => child.HierarchyId.IsDescendantOf(parent.HierarchyId)) .... — Gert Arnold
– Gert Arnold, Commented Nov 19, 2024 at 14:10
Is this really LINQ-to-SQL (the ORM)? If it's Entity Framework then please revise the tags. — Gert Arnold
– Gert Arnold, Commented Nov 19, 2024 at 14:10

Norbert Hüthmayr · Accepted Answer · 2024-11-19 13:55:04Z

Did some more digging and found the following solution

var selfJoin = from child in Context.GlobalDocumentClassification from parent in Context.GlobalDocumentClassification select new { child, parent }; var query = from item in selfJoin where item.child.HierarchyId.IsDescendantOf(item.parent.HierarchyId) && EF.Constant(ids).Contains(item.parent.Id) select item.child.Id;

This produces SQL-Server cross join query and allows for the use of IsDescendantOf() in the queries where clause.

Collectives™ on Stack Overflow

HierarchyID: Get all descendants for a list of parents using LINQ-to-SQL

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related