2

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!

KM.
  • 101,727
  • 34
  • 178
  • 212
Jaime
  • 595
  • 1
  • 8
  • 20
  • A sql linked list... interesting! – Joel Aug 26 '09 at 19:22
  • In case anyone using oracle wants to do the same thing, I will mention that oracle has the "Connect By" clause that will solve this pretty simply, as well as work for more complicated tree structures. – Peter Recore Jan 12 '10 at 19:36

3 Answers3

3

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)
KM.
  • 101,727
  • 34
  • 178
  • 212
  • This will work up to 100 recursions and then you will have to configure SQL Server to allow recursion depth to be higher. – Darrel Miller Aug 26 '09 at 19:46
  • you don't have to change the server default of 100. if you add _OPTION (MAXRECURSION n)_ after the _select * from cteview_ where n can be between 0 and 32,767 levels of recursion (zero has no limit) – KM. Aug 26 '09 at 19:52
  • This looks pretty neat, but I was wondering if I can translate this into a LINQ statement. – Jaime Aug 26 '09 at 19:53
  • +1 very clever. I didn't know such recursive expressions were possible – Adam Ralph Aug 26 '09 at 19:56
  • 1
    Common Table Expression (CTE) in linq-to-sql?: http://stackoverflow.com/questions/584841/common-table-expression-cte-in-linq-to-sql – KM. Aug 26 '09 at 19:58
0

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));
Yuriy Faktorovich
  • 67,283
  • 14
  • 105
  • 142
  • 2
    Wouldn't this give you a result of... lazy dog. brown fox the The quick jumps over ???? – Jaime Aug 26 '09 at 19:40
  • using an application side solution like this, you'd have to return back the entire table and then put all the id+nextIDs back together, or you will have to hit the db with a SELECT for each segment – KM. Aug 27 '09 at 13:48
  • @KM... that is what I'm trying to avoid. Hitting the db every time. – Jaime Aug 28 '09 at 17:07
0

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.

gabe
  • 127
  • 3