Multi-Mapper to create object hierarchy

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.