Get all subitems (children, grandchildren) and parents from a self referencing table
c#, sql-server
Solution
Try using the following recursive `CTE` as a basis for your solution:
WITH TREE AS (
SELECT ROOT.Id
,ROOT.ParentId
,ROOT.Prop1
,ROOT.Prop2
...
,ROOT.PropN
,1 AS Level
FROM ENTITIES ROOT
-- list entities starting from ROOT node (no ParentId)
WHERE ROOT.ParentId IS NULL
-- or list entities starting from a specified node
-- WHERE ROOT.Id = @entityId
UNION ALL
SELECT CHILD.Id
,CHILD.ParentId
,CHILD.Prop1
,CHILD.Prop2
...
,CHILD.PropN
,PARENT.Level + 1
FROM ENTITIES CHILD INNER JOIN
TREE PARENT ON CHILD.ParentId = PARENT.Id
WHERE CHILD.ParentId IS NOT NULL
)
SELECT * FROM TREE
You could place the `CTE` inside a `VIEW` or a `Stored Procedure` if you need to pass parameters.
Problem
I want to create a 'selectable method' (doesn't matter if it is a view or a function or a stored procedure or something else, I don't know if there are other methods) to get all the direct parents and all children, grandchildren, grand-grandchildren etc. records from a self referencing table. The 'selectable method' I want to call from C# (.NET) We have a table called `entities`, and it has (amongst others) a property `parentId` which refers to the `Id` field of the `entities` table. In this way we model the tree of entities (in our case elements of a building, think house, floor, room etc.) The parent nodes, if visualized, are the ones which form a direct line to the root element. The child...nodes can, if visualised, 'spread out'. It's ok if the selection has to be done in two seperate statements. In C# I know how to do this, but I don't want to fire lots of requests to the database and I think Sql Server can do this fast(er) by itself, but I don't know how :-)