Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
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;
}
}
}
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.
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.