dapper - 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.