Dapper multi-mapping - collections are empty

Question

I'm attempting to use Dapper to return a set of Shares and an associated one-to-many collection of ShareItems and ShareHistories. My Dapper call looks like this:

string sql =
    @"select s.Id, s.UserId, s.Name, si.ShareId as Id, si.Name as ItemName
    , sh.ShareId As Id, sh.DateShared, sh.SentTo 
    from Shares s 
    inner join ShareItems si on s.Id = si.ShareId
    inner join ShareHistory sh on s.Id = sh.ShareId
    where s.Id = @shareId";

    return conn.Query<Share, List<ShareItem>, List<ShareHistory>, Share>(
                sql,
                (share, shareItems, history) => 
                    { 
                      share.Items = shareItems; 
                      share.History = history; return share; 
                    },
                new { shareId = shareId }).Single();

When I run the query in SQL I get the flattened data I expect. However, when I run the code through Dapper the Items and History collections are coming back empty. I was screwing around with the splitOn parameter but after reading this question I now understand what splitOn is doing (this would be good to have somewhere on the Dapper site btw) and I think I'm handling that part okay. So what am I doing wrong?

I've edited your code now for you. You should try to avoid getting scrollbars. It helps alot for others. And you're likely to get more answers. — radbyx
– radbyx, Commented Mar 26, 2012 at 15:01

Community · Accepted Answer · 2017-05-23 10:26:34Z

2

I don't think you can populate a deep object graph from 1 row. (Unless all items are in that one row) There's a similar question: Populating a list in a object with dapper

Edit: There's also QueryMultiple - you might want to check that out. It allows the return of multiple resultsets. You could then map your entities.

Query Multiple Example

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Mar 26, 2012 at 19:49

Alex

8,1561 gold badge45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shawn Hubbard Over a year ago

Hm, that would be unfortunate. My current workaround is making a db call per collection, but I was hoping I could get this down to one database call in Dapper.

Alex Over a year ago

Have you checked out the QueryMultiple function? I've added an edit.

Shawn Hubbard Over a year ago

Does QueryMultiple execute as one db hit or one per select? If it's one per select, then I'm not gaining much over the original solution posted above. I did try it though and it does work.

Shawn Hubbard Over a year ago

I'll accept this as the answer since it's what I ended up using. I haven't taken the opportunity to profile and see what is going to SQL, but in the interest of avoiding premature optimization this is working flawlessly.

Collectives™ on Stack Overflow

Dapper multi-mapping - collections are empty

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related