sql - Simulating CTE recursion in C#

Question

Say that have the following CTE that returns the level of some tree data (adjacency model) that I have (taken from Hierarchical data in Linq - options and performance):

WITH hierarchy_cte(id, parent_id, data, lvl) AS
(
    SELECT id, parent_id, data, 0 AS lvl
    FROM dbo.hierarchical_table
    WHERE (parent_id IS NULL)

    UNION ALL

    SELECT t1.id, t1.parent_id, t1.data, h.lvl + 1 AS lvl
    FROM dbo.hierarchical_table AS t1 
    INNER JOIN hierarchy_cte AS h ON t1.parent_id = h.id
)
SELECT id, parent_id, data, lvl
FROM hierarchy_cte AS result

I was wondering if there would be any performance increase by doing the recursion in C# instead of SQL. Can anyone show me how to perform the same work that the CTE does with a recursive C# function assuming I have a IQueryable where Tree is an entity representing an entry in the hierarchical table? Something along the lines of:

public void RecurseTree(IQueryable<Tree> tree, Guid userId, Guid parentId, int level)
{
    ...
    currentNode.level = x
    ...
    Recurse(tree... ,level + 1)
}

Would be cool to see this is easy to do using a lambda expression.

Answer 1

Genuinely recursive lambdas (and by inference, Expressions) are technically possible but pretty much insane. I would also expect any parser (L2S, EF, etc) except maybe LINQ-to-Objects to just go crazy trying to unpick that.

In short: you would do well to consider this an unsupported mechanism for Expression.

Finally, note that just because you are writing an Expression doesn't mean you are executing it in C# - in fact, quite probably the opposite: if you are actively writing an Expression (rather than a delegate or procedural code) I assume that it is going to a parser (unless you used .AsQueryable() to push it to LINQ-to-Objects).