Order a query based on a field pointing to the same table

Question

Hi all,

I have a table called "Sentence" that has the following fields:

ID         <--- OK
NextID     <--- FK To ID
Text

So if I had the following records:

*ID*            *NextID*          *Text*
1               12                The quick
3               40                jumps over
5               null              lazy dog.
12              3                 brown fox
40              5                 the

If I know that the beginning of the sequence is the record with ID = 1, is there a way to order a query based on the sequence of the NextID. As with the example above, the expected result should be...

The quick
brown fox
jumps over
the
lazy dog.

I am looking for either a T-SQL statement/s or somehow do this with Linq. Thanks in advance!

Answer 1

try this:

declare @YourTable table (RowID int primary key, NextID int, TextValue varchar(50))

INSERT INTO @YourTable VALUES (1 , 12  ,'The quick')
INSERT INTO @YourTable VALUES (3 , 40  ,'jumps over')
INSERT INTO @YourTable VALUES (5 , null,'lazy dog.')
INSERT INTO @YourTable VALUES (12, 3   ,'brown fox')
INSERT INTO @YourTable VALUES (40, 5   ,'the')

;with cteview as (
SELECT * FROM @YourTable WHERE RowID=1
UNION ALL
SELECT y.* FROM @YourTable y
    INNER JOIN cteview   c ON y.RowID=c.NextID
) 
select * from cteview
OPTION (MAXRECURSION 9999) --go beyond default 100 levels of recursion to 9999 levels

OUTPUT:

RowID       NextID      TextValue
----------- ----------- --------------------------------------------------
1           12          The quick
12          3           brown fox
3           40          jumps over
40          5           the
5           NULL        lazy dog.

(5 row(s) affected)

Answer 2

LINQ answer:

table.OrderBy(sentence => sentence.NextID);

Edit: I hope I answered it correctly this time:

class Sentence
{
    public int Id;
    public int? NextId;
    public string Text;
    public Sentence(int id, int? nextId, string text)
    {
        this.Id = id;
        this.NextId = nextId;
        this.Text = text;
    }
}

var Sentences = new [] {
    new Sentence(1, 12, "This quick"),
    new Sentence(3, 40, "jumps over"),
    new Sentence(5, null, "lazy dog."),
    new Sentence(12, 3, "brown fox"),
    new Sentence(40, 5, "the"),
};

Func<int?, string> GenerateSentence = null;
GenerateSentence = (id) => id.HasValue? Sentences
    .Where(s => s.Id == id.Value)
    .Select(s => s.Text + " " + GenerateSentence(s.NextId))
    .Single() : string.Empty;

Console.WriteLine(GenerateSentence(1));

Answer 3

If you are using LINQ to SQL/Entities, then the generated Sentence class should have all those properties you mentioned, as well as an entity reference to the next sentence (let's call it NextSentence) from the foreign key.

Then you can just do:

Sentence s = Sentences.First();
StringBuilder sb = new StringBuilder();
do { sb.Append(s.Text); s = s.NextSentence; } while (s != null);

and sb.ToString() will have your answer.