CODEWITHSUNDEEP
c#sql-serverlinq-to-sql
captncraig
captncraig

Reputation: 23068

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

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?

Upvotes: 1

Views: 129

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062600

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.

Upvotes: 1

flesh
flesh

Reputation: 23935

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

Upvotes: 2

Related Questions