使用实体框架编写递归CTE流畅语法或内联语法 [英] Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
问题描述
在SQL和实体框架（ADO.NET实体映射）中，我是新的递归。我正在开发一个注释管理，我有一个评论表，该表包含列 NewsID，CommentID，ParentCommentID，IndentLevel，CreatedTime 。
I am new to this recursion in both SQL and Entity Framework (ADO.NET Entity Mapping). I am working on a comment management where I have a Comments table and the table contains columns NewsID, CommentID, ParentCommentID, IndentLevel, CreatedTime.
我正在尝试获取一个特定新闻项目的注释列表，其中所有注释都根据父级和创建时间下的子级排列，如下所示： / p>
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.
我所倾斜的是（来自互联网资源和以前的stackoverflow Q / A）
What I have leaned so far is (from internet resources and previous stackoverflow Q/A)
- 如图所示，递归查询缓慢。并且使用实体框架来做这个甚至更慢。但是可以实现。
- 所以，可以通过在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
- 但在尝试之前我想要尝试Linq。
为此，我参考了这个链接，我已经得到了这个想法：
https://stackoverflow.com/a/6225373/892788
For this I have refering to this link where I have got the idea: https://stackoverflow.com/a/6225373/892788
但是我已经试图了解代码，但是没有任何意义。有人可以给我一个关于在实体框架中编写递归CTE的更好和详细的解释吗？
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?
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);
我已经尝试过了，似乎我无处可去，虽然有关于SQL递归的说明，Linq的例子不多，而且含糊不清我可以帮助我了解Linq中的这个CTE递归，这是很好的
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
提前感谢
推荐答案
AFAIK不支持LINQ或EF中的递归CTE，解决方案是将CTE暴露为视图，文章递归或hi使用EF代码优先和迁移的错误查询显示了如何使用EF代码首次迁移来部署此类视图。
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 Migrations shows how to deploy such a view using EF code first migrations.
尝试通过执行递归客户端迭代来模拟CTE不会缩放到大数据集，并导致与服务器进行聊天交换。注意你的EF代码如何返回 IEnumerable 不是 IQueryable ，这意味着它实现每个级别，然后连接下一级别每个条目作为单独的请求。基于LINQ的解决方案将适用于具有有限进入计数的浅层次结构（并且注意到许多项目可以具有这样的数据布局，用户帖子/答案是典型的例子），但是将在深层次结构下崩溃许多元素。
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 IEnumerable not 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 can have such data layout, user posts/answers being a typical example), but will crumble under deep hierarchies with many elements.
这篇关于使用实体框架编写递归CTE流畅语法或内联语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
