Automapper: How to map nested recursive collection?

[idotnetdev]

Hi Jimmy,
I always have a problem in automapper for mapping nested collection recursively for such as Menus, Comments, ... .
I want to map children's of an entity with that entity.
I created gist for my entity class and view model class (DTO) in url: https://gist.github.com/idotnetdev/a6946b10d13a2360d2d023f0a171cb70

Mapper.Initialize(cfg =>
            {
                cfg.CreateMissingTypeMaps = true;
                cfg.AddProfile<UserProfile>();
            });
            Mapper.AssertConfigurationIsValid();

Version: 6.1.1.0

public DataGridViewModel<CommentsViewModel> GetComments(int productId, int page, int pageSize)
        {
            var query = _comment.Where(x => x.ProductId == productId).Include(x => x.Parent)
                .Include(x => x.CreatedBy).OrderByDescending(x => x.Id);

            var dataGridViewModel = new DataGridViewModel<CommentsViewModel>
            {
                Records = query.ProjectTo<CommentsViewModel>(parameters: null, configuration: _mapper.ConfigurationProvider)
                    .ToPagedList(page, pageSize),
                TotalCount = query.Count()
            };

            return dataGridViewModel;
        }

[jbogard]

The big problem here is you're also doing a recursive query. EF doesn't just do that for you automatically. Neither does SQL.

Your best bet is to use a common table expression (CTE) to build recursive query into flat DTOs.

Then you'll need to connect parent/child references yourself looping through the object model and setting the references yourself. It's easiest if you use a hashtable first to put each CommentsViewModel in by CommentId, then it's easy to loop through the comments one by one and place them in their parent's list.

Good luck!

[idotnetdev]

Are you have an example code for that you say please?

[jbogard]

CTE to do recursive joins explained:

https://www.essentialsql.com/recursive-ctes-explained/

That, combined with EF's ability to use raw SQL to DTOs gets you a flat list of all comments.

Example code for putting things back together:

```c#
var comments = allComments.ToDictionary(src => src.Id);

foreach (var comment in allComments) {
if (allComments.TryGetValue(comment.ParentCommentId, out var parent) {
parent.ChildComments.Add(comment);
}
}

return comments.ToList();
```

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.