使用 Entity Framework Fluent 语法或 Inline 语法编写递归 CTE [英] Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
问题描述
我是 SQL 和实体框架(ADO.NET 实体映射)中这种递归的新手.我正在处理评论管理，其中有一个 Comments 表，该表包含列 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.
我正在尝试获取特定新闻项目的评论列表，其中所有评论都是根据父项下的子项和创建时间排列的，如下所示:
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 作为视图公开.关于 使用 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.
这篇关于使用 Entity Framework Fluent 语法或 Inline 语法编写递归 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
