3

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?

Community
  • 1
  • 1
Shawn Hubbard
  • 932
  • 10
  • 23
  • 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 Mar 26 '12 at 15:01

1 Answers1

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

Community
  • 1
  • 1
Alex
  • 7,901
  • 1
  • 41
  • 56
  • 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. – Shawn Hubbard Mar 27 '12 at 07:05
  • Have you checked out the QueryMultiple function? I've added an edit. – Alex Mar 27 '12 at 07:19
  • 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 Mar 27 '12 at 15:20
  • 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. – Shawn Hubbard Apr 03 '12 at 18:55