Recursive Cte In Ef Core + Automapper Projectto

In my project I have types Comment and CommentDto: public class Comment { public Guid CommentId { get; set; } public string Content { get; set; } public Guid PostId {

Solution 1:

Recursive CTE returns flat list, then you have to build hierarchy again.

var commentHierarchy = await _context.Comment
    .FromSqlInterpolated($"SELECT CommentId, Content, PostId, ParentCommentId FROM dbo.fn_PostCommentHierarchy('post-id-here')")
    .ProjectTo<CommentDto>(_mapper.ConfigurationProvider)
    .ToListAsync();

var lookup = commentHierarchy.ToLookup(x => x.commentId);

foreach (var c in commentHierarchy)
{
    if (lookup.Contains(c.commentId))
        c.inverseParentComment.AddRange(lookup.Item[c.commentId]);
}

var result = commentHierarchy.Where(c => c.parentCommentId == null);