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.

Answer 1

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.