模拟CTE递归在C＃ [英] Simulating CTE recursion in C#

问题描述

再说说有以下CTE返回一些树数据的水平（邻接模型），我（从的 Linq中的分层数据 - 选项和性能）：

<预类=郎-SQL prettyprint-覆盖> WITH hierarchy_cte（ID，PARENT_ID，数据，LVL）AS
（
选择ID，PARENT_ID，数据，0 AS拉特
FROM dbo.hierarchical_table
WHERE（PARENT_ID IS NULL）

UNION ALL

选择t1.id，t1.parent_id，t1.data，h.lvl + 1 LVL
从dbo.hierarchical_table AS T1
INNER JOIN hierarchy_cte AS h开t1.parent_id = h.id
）
选择ID，PARENT_ID，数据，拉特
从hierarchy_cte AS结果


我不知道是否会有这样做在C＃中，而不是SQL递归的性能提升。谁能告诉我如何执行该CTE确实有一个递归C＃函数假设我有一个IQueryable的，其中树是代表在分级表中的条目实体相同的工作？ $

 公共无效RecurseTree（;树>树的Guid用户ID的Guid parentId的，INT水平的IQueryable<）：沿线的东西b $ b {
 ... 
 currentNode.level = X 
 ... 
递归（树......，水平+ 1）
} 
  

将是冷静地看到，这是很容易使用lambda表达式做。

< DIV CLASS =h2_lin>解决方案

递归在SQL Server是comparsion窘况缓慢的，但它确实工作。

我不得不说，T-SQL会受到一定限制，但它从来没有打算做所有这些操作在首位。我不相信有什么办法可以做到这一点有一个IQueryable如果你inted反对你的SQL Server实例上运行这一点，但你可以在内存中的计算机上运行使用LINQ到对象在一个相对代码。紧凑的方式

下面是做到这一点的一种方法：

 类树节点
 {
公众诠释标识; 
公众诠释？的ParentId; 
} 
 
静态无效的主要（字串[] args）
 {
无功名单=新名单，LT;树节点> {
新的TreeNode {ID = 1 } 
新的TreeNode {ID = 4的ParentId = 1}，
新的TreeNode {n = 5，的ParentId = 1}，
新的TreeNode {ID = 6的ParentId = 1}， 
新的TreeNode {ID = 2}，
新的TreeNode {ID = 7，的ParentId = 2}，
新的TreeNode {ID = 8的ParentId = 7} 
新的TreeNode {ID = 3}，
}; 
 
的foreach（在水平（VAR项目清单，NULL，0））
 {
 Console.WriteLine（ID = {0}，级别= {1}项。重点，item.Value）; 
} 
} 
 
私有静态的IEnumerable< KeyValuePair< INT，INT>>水平（表<树节点>列表，诠释parentId的，诠释拉特？）
 {
返回列表
。凡（X => x.ParentId == parentId的）
 .SelectMany （X => 
新的[] {新KeyValuePair< INT，INT>（x.Id，LVL）} .Concat（电平（列表，x.Id，拉特+ 1））
）; 
} 
  

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.

解决方案

Recursion in SQL Server is horrendously slow by comparsion but it does work.

I'd have to say that T-SQL is somewhat limited but it was never meant to do all those operations in the first place. I don't believe there is any way you can make this happen with an IQueryable if you inted to run this against you SQL Server instance but you can do it in memory on the machine running the code using LINQ-to-Objects in a relatively compact manner.

Here's one way to do that:

class TreeNode
{
    public int Id;
    public int? ParentId;
}

static void Main(string[] args)
{
    var list = new List<TreeNode>{
        new TreeNode{ Id = 1 },
            new TreeNode{ Id = 4, ParentId = 1 },
            new TreeNode{ Id = 5, ParentId = 1 },
            new TreeNode{ Id = 6, ParentId = 1 },
        new TreeNode{ Id = 2 },
            new TreeNode{ Id = 7, ParentId= 2 },
                new TreeNode{ Id = 8, ParentId= 7 },
        new TreeNode{ Id = 3 },
    };

    foreach (var item in Level(list, null, 0))
    {
        Console.WriteLine("Id={0}, Level={1}", item.Key, item.Value);
    }
}

private static IEnumerable<KeyValuePair<int,int>> Level(List<TreeNode> list, int? parentId, int lvl)
{
    return list
        .Where(x => x.ParentId == parentId)
        .SelectMany(x => 
            new[] { new KeyValuePair<int, int>(x.Id, lvl) }.Concat(Level(list, x.Id, lvl + 1))
        );
}

这篇关于模拟CTE递归在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！