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

问题描述

假设有以下CTE返回我拥有的一些树数据（邻接模型）的级别（取自 Linq中的分层数据 - 选项和性能）：

  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 
  

我想知道在C＃而不是SQL中执行递归是否会有任何性能提升。任何人都可以告诉我如何执行CTE与递归C＃函数相同的工作，假设我有一个IQueryable，其中Tree是一个表示层次表中的条目的实体;一些沿着以下行：

  public void RecurseTree（IQueryable< Tree> tree，Guid userId，Guid parentId，int level）
 {
 ... 
 currentNode.level = x 
 ... 
重复（树...，级+ 1）
} 
  

很高兴看到使用lambda表达式很容易做到。

解决方案

在SQL Server中递归是比较缓慢的，但是它的工作正常。

我必须说T-SQL有点有限，但从来没有意图做所有这些操作。我不相信有什么办法可以让这个事情发生在一个IQueryable如果你打算运行这个对你的SQL Server实例，但你可以在内存中运行代码使用LINQ对对象在一个相对的

这是一种方法：

  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（level（list，null，0）中的var项）
 {
 Console.WriteLine（Id = {0}，Level = {1} .Key，item.Value）; 
} 
} 
 
私有静态IEnumerable< KeyValuePair< int，int>>级别（List< TreeNode> list，int？parentId，int lvl）
 {
返回列表
 .Where（x => x.ParentId == parentId）
 .SelectMany （x => 
 new [] {new KeyValuePair< int，int>（x.Id，lvl）} .Concat（Level（list，x.Id，lvl + 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))
        );
}

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