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
– IAbstract, Commented May 27, 2011 at 2: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
– Peter, Commented May 27, 2011 at 2: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
– Marc Gravell, Commented May 27, 2011 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
– Peter, Commented May 30, 2011 at 0:09

Community · Accepted Answer · 2017-05-23 12:30:20Z

12

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.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jun 21, 2011 at 12:04

Sam Saffron

132k81 gold badges336 silver badges511 bronze badges

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

2 Comments

Mr Grok Over a year ago

This doesn't work for me. When it tries to read TSecond, the reader has been consumed and cannot be read from again.

BZink Over a year ago

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

Collectives™ on Stack Overflow

How do I select an aggregate object efficiently using Dapper?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related