Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax

c# entity-framework linq sql-server

Question

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.

What I have leaned so far is (from internet resources and previous 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:

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.

For this I have refering to this link where I have got the idea: 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?

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

Thanks in advance

1
13
5/23/2017 10:29:16 AM

Accepted Answer

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.

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.

27
3/26/2020 2:51:32 PM

Popular Answer

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.

Writing to CTE Query with Linq is NOT possible Common Table Expression (CTE) in linq-to-sql?

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow