Linq-to-sql: How to actively grab linked objects?

Question

So I have a tree structure in a SQL-Server database. Each node is linked to its parent by a foreign key called prev, to another node.

I would like whenever I fetch a node, for all nodes leading to the root of the tree to be fetched as well. Currently I can do it like this:

        MyDataContext db = new MyDataContext();
        IList<Node> nodes = new List<Node>();
        Node node = db.Nodes.Single(x => x.id == 14);
        nodes.Add(node);
        while (node.prev != null)
        {
            node = db.Nodes.Single(x => x.id == node.prev);
            nodes.Add(node);
        }

but this will require a number of queries equal to the depth of my tree. I am a little new to linq-to-sql, so I'm not sure how to tell it to fetch recursively like that. Is it possible?

Answer 1

I think you can do it with some of the DataLoadOptions ..

Answer 2

For a tree structure, what you ask for may be beyond LINQ. There is LoadWith, but that works by trying to add a JOIN etc; which won't work for a tree.

I expect that in this case, calling a UDF would be a good option (moving the interesting code to the database). How to walk the tree depends on your version etc and the exact SQL server version. For example:

CREATE FUNCTION GetWithAncestors(@id int)
RETURNS @result TABLE (id int null, prev int null, name varchar(100) not null)
AS
BEGIN
    ;
    WITH fn (id, prev, name)
     AS (
       SELECT tt.id, tt.prev, tt.name, 0
       FROM TreeTest tt
       WHERE tt.id = @id
       UNION ALL
       SELECT tt.id, tt.prev, tt.name
       FROM fn
       INNER JOIN TreeTest tt
       ON tt.id = fn.prev
     )
    INSERT @result
    SELECT * FROM fn
    RETURN
END
GO

Now may the UDF to your data-context, etc.