Ordering a Linked List-Structure in a SQL or LINQ Query?

Question

I have a database that essentially looks like this:

id uniqueidentifier NOT NULL
data nvarchar
nextid uniqueidentifier NULL

This is a Linked List, as each nextid links to an id in that table, except for the last one, here nextid is NULL. I do know the id of the first node.

I want to SELECT them all in the correct order, given a start id.

Is there a way this can be done in T-SQL (Edit: SQL 2008) or LINQ?

I know I can write code to do it manually in C#, just wondering if I can already query in that order?

Answer 1

Not sure of [SortOrder] makes any difference as I don't have enough data to test it. It allows you to sort in both directions.

with cteList as
(
    select id, data, nextid, 1 as [SortOrder]
    from #TableTemp
    where id = 'E8ADAA52-54F8-4FE3-BE59-9852E52B33F5' --id of the 1st item

    union all

    select #TableTemp.id, #TableTemp.data, #TableTemp.nextid, (cteList.[SortOrder] + 1) as [SortOrder]
    from #TableTemp
    join cteList on #TableTemp.id = cteList.nextid
)
select * from cteList
order by [SortOrder] asc

Answer 2

What version of SQL Server? If it's 2005 or newer you can use a recursive CTE do it.

with linked_list as (
  select
    id, data, nextid
  from
    table
  where
    id = @head
  union all
  select
    t.id, t.data, t.nextid
  from
    table t
    join linked_list ll on t.id = ll.nextid
)
select
  *
from
  linked_list