Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax

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

Answer 1

AFAIK, as of mid-2022, there is still 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.

Answer 2

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.

Answer 3

After spending several hours reading about this issue I decided to do it in C# and not having to create a database view.

NOTE: Use this only for non performance critical operation. Example with 1000 nodes performance 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

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