2

I have a table which has a column for Id and parentId. ParentId contains the Id of another row in the table. If the ParentId is null then it is the top of the hierarchy.

I have the Id of a row and I want to select all rows above it in the hierarchy. Can I do this in a single select?

so in this example:

Id | parentId | other columns
1 | null
2 | 1
3 | 2

if I have id=3 I want to select rows 1,2,3.

Can I do it in linq to sql?

Sam Holder
  • 32,535
  • 13
  • 101
  • 181

3 Answers3

2

You can do it in a single select using a recursive CTE, however LINQ to SQL doesn't support this so you will have to create a stored procedure with the query and call that from LINQ to SQL.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
1

Take a look at this example, uses recursive CTE.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
1

Don't know LINQ, but as other answerers have written, many relational databases support Common Table Expressions (CTE) - but not all (Oracle comes to mind). And if supported, CTE is a good approach to retrieving the "ancestry".

That noted, there are some other approaches to consider in particular a bridge table or nested set. See my question for some explanation of these options and other ways of representing hierarchical data. Briefly, a bridge table most likely updated using CTE from a trigger will easily give you all ancestors or descendants - just not how close. A nested set model will give you this information and how close at the expense of more expensive inserts and updates comparatively.

Community
  • 1
  • 1
orangepips
  • 9,891
  • 6
  • 33
  • 57