Dapper Multi Mapping Not Splitting on Named Parameters

Question

New to Dapper here! Having an issue with multi-mapping. This is my query:

var sql = @"select distinct a.*, 
                c.Id as 'GenreId', c.Active as 'GenreActive', c.Link as 'GenreLink', c.Name as 'GenreName', c.DateCreated as 'GenreDateCreated', c.DateEdited as 'GenreDateEdited',
                d.Id as 'CommentId', d.ReviewId as 'CommentReviewId', d.Name as 'CommentName', d.Email as 'Comment.Email', d.Content as 'CommentContent', d.Active as 'CommentActive', d.DateCreated as 'CommentDateCreated', d.DateEdited as 'CommentDateEdited', d.CommentId as 'ReplyCommentId' 
                from Review a " +
               "left join ReviewGenre b on a.Id = b.ReviewId " +
               "left join Genre c on c.Id = b.ReviewId " +
               "left join Comment d on a.Id = d.ReviewId " +
               "where a.Active = 1 " +
               "order by a.DatePublished desc;"
            ;

And my entities are (shortened for brevity):

public class Review 
{
    public int Id {get;set;}
    public IEnumerable<Genre> Genres { get; set; }
    public IEnumerable<Comment> Comments { get; set; }
}

public class Genre 
{
    public int Id {get;set;}
    public string Name {get;set;}
}

public class Comment 
{
    public int Id {get;set;}
    public int Content {get;set;
}

My query using Dapper tries to split on the renamed columns for Genre.Id and Comment.Id. The query appears to be working fine except none of the Genres and Comments appear to be mapping to the Review class. This is how I am trying to execute the query:

 using (var connection = new SqlConnection(_ConnectionString))
            {
                var reviews = await connection.QueryAsync<Review, Genre, Comment, Review>(
                    sql,
                    (review, genre, comment) =>
                    {
                        review.Genres = new List<Genre>();
                        review.Comments = new List<Comment>();

                        if (genre != null)
                        {
                            review.Genres.ToList().Add(genre);
                        }

                        if (comment != null)
                        {
                            review.Comments.ToList().Add(comment);
                        }

                        return review;
                    },
                    commandType: CommandType.Text,
                    splitOn: "GenreId,CommentId");

                return reviews;
            }

I have researched throughout tutorials and SO on the subject and not finding what could be causing the mapping to not happen.

I would appreciate any suggestions (newbie to Dapper). Thanks!

Answer 1

At this line

review.Genres.ToList().Add(genre);

you are creating each time a new list (.ToList()). This method returns/creates new instance, but the new instance is never assigned back to the model property. It's like doing something like that:

var list = new List<int>();
new List<int>().Add(1);

The two instances are separate objects.

What you can do is to changed your models to work like this (the lists are instantiated with the creation of the object):

public class Review 
{
    public int Id {get;set;}
    public List<Genre> Genres { get; set; } = new List<Genre>();
    public List<Comment> Comments { get; set; } = new List<Comment>();
}

and then adding elements like this:

review.Genres.Add(genre);

Or you can check the original dapper tutorial where they are using dictionary as state manager to remove duplicates.