Is using LinqToSql in a recursive method very bad for performance

I have the following method which get all parents for a node using LinqToSql but I don't know how much it's bad 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();
    }
}

Is it good !! or any idea to improve it !!

Thanks.

As such, above code is walking parent-child hierarchy in upward (parent's) direction. So in worst case, it would result in making n queries to database for hierarchy depth of n. I would suggest you to try deferred execution by changing method slightly such as

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;
}

I am not 100% sure if it would work but idea is to exploit expression trees/deferred execution so that multiple queries are fired within single database trip.

Yet another idea would be to write a stored proc/view that will returns all parent (look at CTE in sql server for the same).

EDIT: Used Where instead of First for finding parent in above code because First will certainly be evaluated immediately - (warning: still untested code)

This will result in a query for every single parent node.

The best to approach this is too either write a stored procedure using CTE or if aforementioned not possible, do a breadth first search/query. The latter will require a query for every level, but will result in much less queries overall.

I'm not sure that there's much less you can do, this is - off the top of my head - the same but not recursive and possibly therefore a bit more efficient - but the issue is always going to be the query 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;

It depends on how big your hierarchy is likely to be. If you know that it is never going to need to recurse that many times is isnt a problem, however it maybe quicker to just load the entire table into memory rather than sending multiple calls to the db.