Uses LinqToSql in a recursive method very poorly for performance

Question

I have the following method that allows all parents to use node using LinqToSql, but I don't know how bad this is for performance.

From NodeTable:

public partial class Node   
{
    public List<Node> GetAllParents(IEnumerable<Node> records)
    {
        if (this.ParentID == 0)
        {
            // Reach the parent, so create the instance of the collection and brake recursive.
            return new List<Node>();
        }

        var parent = records.First(p => p.ID == ParentID);

        // create a collection from one item to concat it with the all parents.
        IEnumerable<Node> lst = new Node[] { parent };

        lst = lst.Concat(parent.GetAllParents(records));

        return lst.ToList();
    }
}

Good! or any idea to improve it!

Thank.

Answer 1

So the above code is a parent-child walking hierarchy in the up (parent) direction. Therefore, in the worst case, this will lead to n queries being loaded into the database for the hierarchy depth n. I suggest you try deferred execution by slightly changing the method, e.g.

public IEnumerable<Node> GetAllParents(IEnumerable<Node> records)
{
        if (this.ParentID == 0)
        {
            // Reach the parent, so create the instance of the collection and brake recursive.
            return new List<Node>();
        }

        var parent = records.Where(p => p.ID == ParentID);
        var parents = parent.Concat(parent.GetAllParents(records));

        return parent;
}

100%, , , / , .

, proc/view, ( CTE sql- ).

: Where First , - (: )

Answer 2

node.

- CTE, , , / . , .

Answer 3

I'm not sure that you can do much less, it’s from my head the same, but not recursive and, perhaps, therefore a little more efficient, but the problem will always be a question for the parent.

List<Node> parentList = new List<Node>();
Node current = this;
while (current.ParentID != 0)
{
    // current = this.Parent;
    current = records.First(r => r.ID == current.ParentID);
    parentList.Add(current)
}

return parentList;

Answer 4

It depends on how big your hierarchy is. If you know that you will never need to repeat that many times this is not a problem, however, it may be faster to load the entire table into memory, rather than sending several calls to db.