编写使用实体框架流利的语法或内嵌语法递归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.

我想获得的对，所有的意见都按照父母在孩子创造时间安排一个特定的新闻条目评论列表，如下图所示：

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.

我迄今俯下身是（从互联网上的资源和previous计算器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。



有关这个我指的是这个链接，我有这个想法：
http://stackoverflow.com/a/6225373/892788

For this I have refering to this link where I have got the idea: http://stackoverflow.com/a/6225373/892788

不过，我试图了解code，但徒劳无功。有人可以给我一个更好的和详细的解释关于实体框架编写递归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

在此先感谢

推荐答案

据我所知没有在LINQ也不在EF递归CTE的不支持。解决的办法是，露出的CTE作为视图。使用EF code首先和迁移递归或分层查询文章>展示了如何使用EF code首先迁移部署这样的观点。

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 code返回的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屋！