Recursive CTE in EF Core + Automapper ProjectTo

Question

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 { get; set; }
    public virtual Post Post { get; set; }

    public Guid? ParentCommentId { get; set; }
    public virtual Comment ParentComment { get; set; }
    public virtual ICollection<Comment> InverseParentComment { get; set; }
}
class CommentDto
{
    public Guid CommentId { get; set; }
    public string Content { get; set; }
    public Guid? ParentCommentId { get; set; }
    public ICollection<CommentDto> InverseParentComment { get; set; }
}

Comment will be mapped into CommentDto. Here is the configuration:

cfg.CreateMap<Comment, CommentDto>();

I have the following recursive CTE, encapsulated inside of table-valued Function:

FUNCTION [dbo].[fn_PostCommentHierarchy] (@postId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN
(
    WITH cte AS
    (
        SELECT CommentId, Content, PostId, ParentCommentId 
        FROM dbo.Comment
        WHERE ParentCommentId IS NULL and PostId = @postId

        UNION ALL

        SELECT child.CommentId, child.Content, child.PostId, child.ParentCommentId
        FROM dbo.Comment child
        INNER JOIN cte parent
        ON parent.CommentId = child.ParentCommentId
        WHERE parent.PostId = @postId
    )

    SELECT * FROM cte
);

This function allows to get the hierarchy of comments of the given post (it takes the ID of a post).

There are comments stored in the database:

To make it more readable, I represent it in hierarchically order (only Content property):

• Hello World!
  • Are you a programmer?
    • Sure
    • What?
• I wanna go to Mars too!
  • See you on the Moon :)

Call function fn_PostCommentHierarchy using EF Core

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

EF Core sends the following SQL-query to SQL-Server:
SELECT CommentId, Content, PostId, ParentCommentId FROM dbo.fn_PostCommentHierarchy('post-id-here')

The code above works as expected (JSON-format was used to increase the readability):

[
{
    "commentId": "be02742a-9170-4335-afe7-3c7c22684424",
    "content": "Hello World!",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": null,
    "parentComment": null,
    "commentRates": [],
    "inverseParentComment": [
    {
        "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "content": "Are you a programmer?",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
        "commentRates": [],
        "inverseParentComment": [
        {
            "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
            "content": "Sure",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
            "commentRates": [],
            "inverseParentComment": []
        },
        {
            "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
            "content": "What?",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
            "commentRates": [],
            "inverseParentComment": []
        }
        ]
    }
    ]
},
{
    "commentId": "cfe126b3-4601-4432-8c87-445c1362a225",
    "content": "I wanna go to Mars too!",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": null,
    "parentComment": null,
    "commentRates": [],
    "inverseParentComment": [
    {
        "commentId": "ab6d6b49-d772-48cd-9477-8d40f133c37a",
        "content": "See you on the Moon :)",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "cfe126b3-4601-4432-8c87-445c1362a225",
        "commentRates": [],
        "inverseParentComment": []
    }
    ]
},
{
    "commentId": "ab6d6b49-d772-48cd-9477-8d40f133c37a",
    "content": "See you on the Moon :)",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": "cfe126b3-4601-4432-8c87-445c1362a225",
    "parentComment": {
        "commentId": "cfe126b3-4601-4432-8c87-445c1362a225",
        "content": "I wanna go to Mars too!",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": null,
        "parentComment": null,
        "commentRates": [],
        "inverseParentComment": []
    },
    "commentRates": [],
    "inverseParentComment": []
},
{
    "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "content": "Are you a programmer?",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
    "parentComment": {
        "commentId": "be02742a-9170-4335-afe7-3c7c22684424",
        "content": "Hello World!",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": null,
        "parentComment": null,
        "commentRates": [],
        "inverseParentComment": []
    },
    "commentRates": [],
    "inverseParentComment": [
    {
        "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
        "content": "Sure",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "commentRates": [],
        "inverseParentComment": []
    },
    {
        "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
        "content": "What?",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "commentRates": [],
        "inverseParentComment": []
    }
    ]
},
{
    "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
    "content": "Sure",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "parentComment": {
        "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "content": "Are you a programmer?",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
        "parentComment": {
            "commentId": "be02742a-9170-4335-afe7-3c7c22684424",
            "content": "Hello World!",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": null,
            "parentComment": null,
            "commentRates": [],
            "inverseParentComment": []
        },
        "commentRates": [],
        "inverseParentComment": [
        {
            "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
            "content": "What?",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
            "commentRates": [],
            "inverseParentComment": []
        }
        ]
    },
    "commentRates": [],
    "inverseParentComment": []
},
{
    "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
    "content": "What?",
    "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
    "post": null,
    "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "parentComment": {
        "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "content": "Are you a programmer?",
        "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
        "post": null,
        "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
        "parentComment": {
            "commentId": "be02742a-9170-4335-afe7-3c7c22684424",
            "content": "Hello World!",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": null,
            "parentComment": null,
            "commentRates": [],
            "inverseParentComment": []
        },
        "commentRates": [],
        "inverseParentComment": [
        {
            "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
            "content": "Sure",
            "postId": "69f3ca3a-66fc-4142-873d-01e950d83adf",
            "post": null,
            "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
            "commentRates": [],
            "inverseParentComment": []
        }
        ]
    },
    "commentRates": [],
    "inverseParentComment": []
}]

Note: I made bold comments, that don't have a parent (root comments).

Map Comment into CommentDto

The code above works with entity-type Comment, but I want to map it into CommentDto. So, let's use ProjectTo for this purpose:

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

Note: _mapper is an object of type IMapper.

I think, the result should be similar to the result, that I got before using ProjectTo. But it looks like:

[
{
    "commentId": "be02742a-9170-4335-afe7-3c7c22684424",
    "content": "Hello World!",
    "parentCommentId": null,
    "inverseParentComment": [
    {
        "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "content": "Are you a programmer?",
        "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
        "inverseParentComment": null
    }
    ]
},
{
    "commentId": "cfe126b3-4601-4432-8c87-445c1362a225",
    "content": "I wanna go to Mars too!",
    "parentCommentId": null,
    "inverseParentComment": [
    {
        "commentId": "ab6d6b49-d772-48cd-9477-8d40f133c37a",
        "content": "See you on the Moon :)",
        "parentCommentId": "cfe126b3-4601-4432-8c87-445c1362a225",
        "inverseParentComment": null
    }
    ]
},
{
    "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
    "content": "Sure",
    "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "inverseParentComment": []
},
{
    "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
    "content": "What?",
    "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "inverseParentComment": []
},
{
    "commentId": "59656765-d1ed-4648-8696-7d576ab7419f",
    "content": "Are you a programmer?",
    "parentCommentId": "be02742a-9170-4335-afe7-3c7c22684424",
    "inverseParentComment": [
    {
        "commentId": "0bb77a43-c7bb-482f-9bf8-55c4050974da",
        "content": "Sure",
        "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "inverseParentComment": null
    },
    {
        "commentId": "b8d61cfd-d274-4dae-a2be-72e08cfa9066",
        "content": "What?",
        "parentCommentId": "59656765-d1ed-4648-8696-7d576ab7419f",
        "inverseParentComment": null
    }
    ]
},
{
    "commentId": "ab6d6b49-d772-48cd-9477-8d40f133c37a",
    "content": "See you on the Moon :)",
    "parentCommentId": "cfe126b3-4601-4432-8c87-445c1362a225",
    "inverseParentComment": []
}
]

Note: I made bold comments, that don't have a parent (root comments).
Compare the result before using ProjectTo and after. Why they are different?

For the code above EF Core sends the following SQL-query to SQL-server:

SELECT [c].[CommentId], [c].[Content], [c].[ParentCommentId], [c0].[CommentId], [c0].[Content], [c0].[ParentCommentId]
FROM (
    SELECT CommentId, Content, PostId, ParentCommentId FROM dbo.fn_PostCommentHierarchy('69f3ca3a-66fc-4142-873d-01e950d83adf')
) AS [c]
LEFT JOIN [Comment] AS [c0] ON [c].[CommentId] = [c0].[ParentCommentId]
ORDER BY [c].[CommentId], [c0].[CommentId]

Questions

Why the result before using ProjectTo and the result after using ProjectTo are not the same? How to fix this problem?

Update 1

According to Svyatoslav Danyliv:

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

But why in this case I should use recursive CTE?
The following solution works in the same way:

List<CommentDto> commentFlatList = await _context.Comment
    .Where(c => c.PostId == Guid.Parse("post-id-here"))
    .ProjectTo<CommentDto>(_mapper.ConfigurationProvider)
    .ToListAsync();
                
Dictionary<Guid, CommentDto> commentDictionary = commentFlatList
    .ToDictionary(c => c.CommentId);

foreach (var comment in commentFlatList)
{
    if (comment.ParentCommentId == null)
    {
        continue;
    }

    if (commentDictionary.TryGetValue((Guid) comment.ParentCommentId, out CommentDto parent))
    {
        parent.Children.Add(comment);
    }
}

List<CommentDto> commentHierarchy = commentFlatList.Where(c => c.ParentCommentId == null);

Note: I used Dictionary instead of Lookup (see this example), but it doesn't change the idea.

Update 2

Let's have a look at the code from the Update 1:

List<CommentDto> commentFlatList = await _context.Comment
        .Where(c => c.PostId == Guid.Parse("post-id-here"))
        .ProjectTo<CommentDto>(_mapper.ConfigurationProvider)
        .ToListAsync();

It will be translated by EF Core into the following:

exec sp_executesql N'SELECT [c].[CommentId], [c].[Content], [c].[ParentCommentId]
FROM [Comment] AS [c]
WHERE [c].[PostId] = @__request_PostId_0',N'@__request_PostId_0 uniqueidentifier',@__request_PostId_0='post-id-here'

Answer 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);