Multi-Mapper to create object hierarchy

Question

I've been playing around with this for a bit, because it seems like it feels a lot like the documented posts/users example, but its slightly different and isn't working for me.

Assuming the following simplified setup (a contact has multiple phone numbers):

public class Contact {     public int ContactID { get; set; }     public string ContactName { get; set; }     public IEnumerable<Phone> Phones { get; set; } }  public class Phone {     public int PhoneId { get; set; }     public int ContactID { get; set; } // foreign key     public string Number { get; set; }     public string Type { get; set; }     public bool IsActive { get; set; } } 

I'd love to end up with something that returns a Contact with multiple Phone objects. That way, if I had 2 contacts, with 2 phones each, my SQL would return a join of those as a result set with 4 total rows. Then Dapper would pop out 2 contact objects with two phones each.

Here is the SQL in the stored procedure:

SELECT * FROM Contacts     LEFT OUTER JOIN Phones ON Phones.ReferenceId=Contacts.ReferenceId WHERE clientid=1 

I tried this, but ended up with 4 Tuples (which is OK, but not what I was hoping for... it just means I still have to re-normalize the result):

var x = cn.Query<Contact, Phone, Tuple<Contact, Phone>>("sproc_Contacts_SelectByClient",                               (co, ph) => Tuple.Create(co, ph),                                            splitOn: "PhoneId", param: p,                                            commandType: CommandType.StoredProcedure); 

and when I try another method (below), I get an exception of "Unable to cast object of type 'System.Int32' to type 'System.Collections.Generic.IEnumerable`1[Phone]'."

var x = cn.Query<Contact, IEnumerable<Phone>, Contact>("sproc_Contacts_SelectByClient",                                (co, ph) => { co.Phones = ph; return co; },                                               splitOn: "PhoneId", param: p,                                              commandType: CommandType.StoredProcedure); 

Am I just doing something wrong? It seems just like the posts/owner example, except that I'm going from the parent to the child instead of the child to the parent.

Thanks in advance

Answer 1

You are doing nothing wrong, it is just not the way the API was designed. All the Query APIs will always return an object per database row.

So, this works well on the many -> one direction, but less well for the one -> many multi-map.

There are 2 issues here:

1. If we introduce a built-in mapper that works with your query, we would be expected to "discard" duplicate data. (Contacts.* is duplicated in your query)

2. If we design it to work with a one -> many pair, we will need some sort of identity map. Which adds complexity.

---

Take for example this query which is efficient if you just need to pull a limited number of records, if you push this up to a million stuff get trickier, cause you need to stream and can not load everything into memory:

var sql = "set nocount on DECLARE @t TABLE(ContactID int,  ContactName nvarchar(100)) INSERT @t SELECT * FROM Contacts WHERE clientid=1 set nocount off  SELECT * FROM @t  SELECT * FROM Phone where ContactId in (select t.ContactId from @t t)" 

What you could do is extend the GridReader to allow for the remapping:

var mapped = cnn.QueryMultiple(sql)    .Map<Contact,Phone, int>     (        contact => contact.ContactID,         phone => phone.ContactID,        (contact, phones) => { contact.Phones = phones };       ); 

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; } 

Since this is a bit tricky and complex, with caveats. I am not leaning towards including this in core.

Answer 2

FYI - I got Sam's answer working by doing the following:

First, I added a class file called "Extensions.cs". I had to change the "this" keyword to "reader" in two places:

using System; using System.Collections.Generic; using System.Linq; using Dapper;  namespace TestMySQL.Helpers {     public static class Extensions     {         public static IEnumerable<TFirst> Map<TFirst, TSecond, TKey>             (             this Dapper.SqlMapper.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;         }     } } 

Second, I added the following method, modifying the last parameter:

public IEnumerable<Contact> GetContactsAndPhoneNumbers() {     var sql = @" SELECT * FROM Contacts WHERE clientid=1 SELECT * FROM Phone where ContactId in (select ContactId FROM Contacts WHERE clientid=1)";      using (var connection = GetOpenConnection())     {         var mapped = connection.QueryMultiple(sql)                 .Map<Contact,Phone, int>     (                     contact => contact.ContactID,                     phone => phone.ContactID,             (contact, phones) => { contact.Phones = phones; }               );          return mapped;     } }