Multi-Mapper to create object hierarchy
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
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.
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;
}
}
Check out https://www.tritac.com/blog/dappernet-by-example/ You could do something like this:
public class Shop {
public int? Id {get;set;}
public string Name {get;set;}
public string Url {get;set;}
public IList<Account> Accounts {get;set;}
}
public class Account {
public int? Id {get;set;}
public string Name {get;set;}
public string Address {get;set;}
public string Country {get;set;}
public int ShopId {get;set;}
}
var lookup = new Dictionary<int, Shop>()
conn.Query<Shop, Account, Shop>(@"
SELECT s.*, a.*
FROM Shop s
INNER JOIN Account a ON s.ShopId = a.ShopId
", (s, a) => {
Shop shop;
if (!lookup.TryGetValue(s.Id, out shop)) {
lookup.Add(s.Id, shop = s);
}
shop.Accounts.Add(a);
return shop;
},
).AsQueryable();
var resultList = lookup.Values;
I got this from the dapper.net tests: https://code.google.com/p/dapper-dot-net/source/browse/Tests/Tests.cs#1343