C# 使用 Entity Framework Fluent 语法或 Inline 语法编写递归 CTE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11929535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
提问by diyoda_
I am new to this recursionin both SQL and Entity Framework (ADO.NET Entity Mapping). I am working on a comment management where I have a Commentstable and the table contains columns NewsID, CommentID, ParentCommentID, IndentLevel, CreatedTime.
我是SQL 和实体框架（ADO.NET 实体映射）中这种递归的新手。我正在做一个评论管理，我有一个Comments表格，表格包含列NewsID, CommentID, ParentCommentID, IndentLevel, CreatedTime。
I am trying to get a list of comments for a particular news item where all the comments are arranged according to the child under parent and created time, as shown below:
我正在尝试获取特定新闻项目的评论列表，其中所有评论都是根据父项下的子项和创建时间排列的，如下所示：
CommentID | time | ParentCommentID
Guid1 | t1 | null
Guid4 | t4 | Guid1
Guid2 | t2 | null
Guid3 | t3 | Guid2
Priority has to be given to the child parent relationship and then the created time.
必须优先考虑子父关系，然后是创建时间。
What I have leaned so far is (from internet resources and previous stackoverflow Q/A)
到目前为止我所学习的是（来自互联网资源和之前的 stackoverflow Q/A）
- As illustrated these recursive queries are slow. and doing this using Entity Framework is even slower. But it can be achieved.
- So, It can be done by creating a stored procedure in SQL Server and calling it by using a functional import. Another thing is using Linq in Entity Framework.
- In SQL Server it is used in this format
- 如图所示，这些递归查询很慢。使用实体框架执行此操作甚至更慢。但它是可以实现的。
- 因此，可以通过在 SQL Server 中创建存储过程并使用函数导入调用它来完成。另一件事是在实体框架中使用 Linq。
- 在 SQL Server 中，它以这种格式使用
SQL:
查询语句：
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
- But before trying this I want to try the Linq.
- 但在尝试之前，我想尝试一下 Linq。
For this I have refering to this link where I have got the idea: https://stackoverflow.com/a/6225373/892788
为此，我参考了我有想法的链接：https: //stackoverflow.com/a/6225373/892788
But I have tried to understand the code but in vain. Can someone give me a better and detailed explanation about writing recursive CTE in Entity Framework?
但我试图理解代码但徒劳无功。有人能给我一个关于在实体框架中编写递归 CTE 的更好更详细的解释吗？
private IEnumerable<NewsComment> ArrangeComments(IEnumerable<NewsComment> commentsList, string parentNewsComntID, int level)
{
Guid parentNewsCommentID;
if (parentNewsComntID != null)
{
parentNewsCommentID = new Guid(parentNewsComntID);
}
else
parentNewsCommentID = Guid.Empty;
return commentsList.Where(x => x.ParentCommentID == parentNewsCommentID).SelectMany(x => new[] { x }.Concat(ArrangeComments(commentsList, x.NewsCommentID.ToString(), level + 1));
}
And I am using this as below inside a method:
我在一个方法中使用如下：
return ArrangeComments(commentList,null , 0);
I have tried them and it seems I am getting nowhere. Though there are explanations on the SQL recursion there are less examples for Linq and are vague for me due to less familiarity. Can somebody help me to understand this CTE recursion in Linq that is great
我已经尝试过，但似乎一无所获。虽然有关于 SQL 递归的解释，但 Linq 的例子较少，而且由于不太熟悉，对我来说也很模糊。有人能帮我理解 Linq 中的这个 CTE 递归吗？
Thanks in advance
提前致谢
采纳答案by Remus Rusanu
AFAIK there is no support for recursive CTEs in LINQ nor in EF. The solution is to expose the CTE as a view. The article on Recursive or hierarchical queries using EF Code First and Migrationsshows how to deploy such a view using EF code first migrations.
AFAIK 在 LINQ 和 EF 中都不支持递归 CTE。解决方案是将 CTE 作为视图公开。关于使用 EF 代码优先和迁移的递归或分层查询的文章展示了如何使用 EF 代码优先迁移来部署这样的视图。
Attempting to emulate CTEs by doing recursive client side iterations does not scale to large data sets and results in a chatty exchange with the server. Note how your EF code returns IEnumerablenot IQueryable, it means that it materializes each level and then concatenates the next level for each entry as a separate request. The LINQ based solution will work reasonably for shallow hierarchies with limited entry count (and note that many projects canhave such data layout, user posts/answers being a typical example), but will crumble under deep hierarchies with many elements.
尝试通过递归客户端迭代来模拟 CTE 不会扩展到大型数据集，并会导致与服务器进行繁琐的交换。请注意您的 EF 代码如何IEnumerable不返回IQueryable，这意味着它实现了每个级别，然后将每个条目的下一个级别作为单独的请求连接起来。基于 LINQ 的解决方案对于条目数量有限的浅层次结构将合理地工作（并注意许多项目可以具有这样的数据布局，用户帖子/答案是一个典型示例），但在具有许多元素的深层次结构下会崩溃。
回答by Marty
Put the CTE query to the StoredProcedure, and then call it from Code. EF provides all the mean for doing that (calling SP and retrieving results). I did the same for myself, works fine.
将 CTE 查询放到 StoredProcedure 中，然后从 Code 中调用它。EF 提供了这样做的所有方法（调用 SP 并检索结果）。我为自己做了同样的事情，效果很好。
Writing to CTE Query with Linq is NOT possible Common Table Expression (CTE) in linq-to-sql?
在 linq-to-sql 中不可能使用 Linq 写入 CTE 查询 ？
The Sample ArrangeComments is a recursive procedure that call itself, but I dare questioning it's performance. It pulls the records from DB and then applies operations in memory.
Sample ArrangeComments 是一个调用自身的递归过程，但我敢于质疑它的性能。它从数据库中提取记录，然后在内存中应用操作。
回答by Ogglas
After spending several hours reading about this issue I decided to do it in C# and not having to create a database view.
在花了几个小时阅读这个问题后，我决定用 C# 来做，而不必创建数据库视图。
NOTE:Use this only for non performance critical operation. Example with 1000 nodes performance from http://nosalan.blogspot.se/2012/09/hierarchical-data-and-entity-framework-4.html.
注意：这仅用于非性能关键操作。来自http://nosalan.blogspot.se/2012/09/hierarchical-data-and-entity-framework-4.html 的1000 个节点性能示例。
Loading 1000 cat. with navigation properties took 15259 ms
Loading 1000 cat. with stored procedure took 169 ms
Code:
代码：
public class Category
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string Name { get; set; }
public int? ParentId { get; set; }
public virtual Category Parent { get; set; }
public virtual ICollection<Category> Children { get; set; }
private IList<Category> allParentsList = new List<Category>();
public IEnumerable<Category> AllParents()
{
var parent = Parent;
while (!(parent is null))
{
allParentsList.Add(parent);
parent = parent.Parent;
}
return allParentsList;
}
public IEnumerable<Category> AllChildren()
{
yield return this;
foreach (var child in Children)
foreach (var granChild in child.AllChildren())
{
yield return granChild;
}
}
}
