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!

vasil oreshenski · Accepted Answer · 2019-10-27T18:40:31.307

2

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.

edited Oct 27 '19 at 18:40

answered Oct 27 '19 at 17:55

vasil oreshenski

2,788
1
14
21

Oops, but it only mapped empty list items. Shouldn't it still map with the columns renamed like I did in the query above? – jallen Oct 27 '19 at 18:13
That was it. I will not break on aliased columns. It works now if I say select * from all three tables and let it break on the Id column. – jallen Oct 27 '19 at 18:16
@jallen Something to consider - due to the one to many join - dapper will return mutiple instances for the same Review record - several Review objects will have the same Id's, so you can check the link of the dapper tutorial in the answer and reuse the same technique. – vasil oreshenski Oct 27 '19 at 18:35
I had it that way initially, but I didn't see a difference because I was getting the same results. I'll revert to the way the tutorial shows how to do it. – jallen Oct 27 '19 at 23:03

Dapper Multi Mapping Not Splitting on Named Parameters

1 Answers1