Multi-Mapper to create object hierarchy

dapper, multi-mapping

Solution

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:

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)

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.

Problem

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.

Original source

Related problems