Writing a Recursive CTE Using the Entity Framework Free Inline or Syntax

Question

I am new to this recursion in both SQL and Entity Framework (ADO.NET Entity Mapping). I'm working on managing comments, where I have a Comments table, and the table contains the columns NewsID, CommentID, ParentCommentID, IndentLevel, CreatedTime .

I am trying to get a list of comments for a specific news item, where all the comments are ordered according to the child under the parent and created time, as shown below:

 CommentID | time | ParentCommentID Guid1 | t1 | null Guid4 | t4 | Guid1 Guid2 | t2 | null Guid3 | t3 | Guid2 

Priority should be given to the parent relationship with the child, and then the time created.

So far I cant (from internet resources and previous stackoverflow Q / A)

• As shown, these recursive queries are slow. and doing it with the Entity Framework is even slower. But it can be achieved.
• So, you can do this by creating a stored procedure in SQL Server and invoking it using functional import. Another thing is to use Linq in the 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 Linq.

For this, I refer to this link, where I have an idea: https://stackoverflow.com/a/165389/

But I tried to understand the code, but in vain. Can someone give me a better and more detailed explanation about writing a recursive CTE in the 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 use this, as shown below, inside the method:

 return ArrangeComments(commentList,null , 0); 

I tried them, and it seems like I'm not going anywhere. Although there are explanations for SQL recursion, there are fewer examples for Linq and are clear to me due to less familiarity. Can someone help me understand this CTE recursion in Linq which is great

Thank you in advance

Answer 1

AFAIK there is no support for recursive CTEs in LINQ or EF. The solution is to show the CTE as a presentation. In the article Recursive or hierarchical queries using EF Code First and Migrations show how to deploy such a view using the first EF code migrations.

Attempting to emulate CTE by performing recursive iterations on the client side does not scale for large data sets and leads to the exchange of chat with the server. Notice how your EF code returns IEnumerable not IQueryable , which means that it materializes each level and then combines the next level for each record as a separate query. A LINQ-based solution will work reasonably for shallow hierarchies with a limited number of records (and note that many projects may have this data format, custom messages / replies are a typical example), but collapses under deep hierarchies with many elements.

Answer 2

Put the CTE request in StoredProcedure and then call it from the code. EF provides all the mean for this (calling SP and getting results). I did the same for myself, works great.

Writing a CTE request with Linq is NOT possible Common table expression (CTE) in linq-to-sql?

Example ArrangeComments is a recursive procedure that calls itself, but I dare to talk about its performance. It extracts records from the database and then applies the operations in memory.

Answer 3

After hours of reading this problem, I decided to do it in C # and not create a database view.

NOTE. Use it only for critical work without performance. An example with a performance of 1000 nodes from http://nosalan.blogspot.se/2012/09/hierarchical-data-and-entity-framework-4.html .

 Loading 1000 cat. with navigation properties took 15259 ms Loading 1000 cat. with stored procedure took 169 ms 

the 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; } } }