Can I select full hierarchy of parents when id and parent id are in the same table?

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?

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.

Take a look at this example, uses recursive CTE.

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.