How do I select an aggregate object efficiently using Dapper?

Question

Lets say that I have a series of objects that form an aggregate.

public class C{
 public string Details {get;set;}
}

public class B{
  public string Details {get;set;}
  public List<C> Items {get;set;}
}

public class A{
  public long ID {get;set;}
  public string Details {get;set;}
  public List<B> Items {get;set;}
}

using Dapper, what is the best way to populate these from tables in a database (in my case it's postgres but that shouldn't matter). The tables in the example are pretty much one for one with the object model. The Items property on the class representing foreign key relationships to each subordinate object. i.e. 3 tables, A has a One to Many relationship with B, B has a One to Many relationship with C.

So for a given ID of A I want my objects to have all their child data as well.

My best guess is that I should use QueryMultiple somehow but I am not sure how best to do it.

Maybe the `QueryMultiple` method? I've just downloaded dapper .. assuming you are referring to 'dapper-dot-net'. — IAbstract, May 27 '11 at 02:10
What I'm currently doing is a QueryMultiple over all the different tables and then using linq to assign by id to the child collections. it means I do one batch to the database and do all the work in ram rather than what I've seen linq2sql do which is a shed-load of incidental queries to the db...I'm pretty happy with the result I just wondered if my approach was/is the intent of the dapper author. — Peter, May 27 '11 at 02:43
At the moment we don't run an identity manager, so you'd get dups. I'll have to have a play to work around that. — Marc Gravell, May 27 '11 at 16:26
Thanks @Marc, I look forward to seeing what you come up with. I'm pretty happy with the manual way I'm handling it at the moment but Identity management would be a bonus. — Peter, May 30 '11 at 00:09
@Peter I have been thinking about this problem, but can not come up with an elegant API I like, any suggestions? — Sam Saffron, May 31 '11 at 02:58

score 12 · Accepted Answer · edited May 23 '17 at 12:30

I think the helper I propose here: Multi-Mapper to create object hierarchy may be of help.

var mapped = cnn.QueryMultiple(sql)
   .Map<A,B,A>
    (
       A => A.ID, 
       B => B.AID,
       a, bees => { A.Items = bees};  
    );

Assuming you extend your GridReader and with a mapper:

public static IEnumerable<TFirst> Map<TFirst, TSecond, TKey>
    (
    this GridReader reader,
    Func<TFirst, TKey> firstKey, 
    Func<TSecond, TKey> secondKey, 
    Action<TFirst, IEnumerable<TSecond>> addChildren
    )
{
    var first = reader.Read<TFirst>().ToList();
    var childMap = reader
        .Read<TSecond>()
        .GroupBy(s => secondKey(s))
        .ToDictionary(g => g.Key, g => g.AsEnumerable());

    foreach (var item in first)
    {
        IEnumerable<TSecond> children;
        if(childMap.TryGetValue(firstKey(item), out children))
        {
            addChildren(item,children);
        }
    }

    return first;
}

You could extend this pattern to work with a 3 level hierarchy.

This doesn't work for me. When it tries to read TSecond, the reader has been consumed and cannot be read from again. — Mr Grok, Jul 12 '11 at 14:30
Thanks Sam, I did get it working by modifying my query. Used something like what you suggested in this post [SO help with multi-mapper](http://stackoverflow.com/questions/6379155/help-with-multi-mapper-to-create-object-hierarchy/6380756#6380756) — BZink, Jul 28 '11 at 22:34

How do I select an aggregate object efficiently using Dapper?

1 Answers1

Linked