多映射器创建对象层次结构 [英] 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; }
}
我希望最终得到返回多个联系人的信息电话对象。这样，如果我有2个联系人，每个联系人有2部手机，那么我的SQL将返回这些联系人的联接，结果集共有4行。然后Dapper弹出两个带有两个电话的联系人对象。
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.
这是存储过程中的SQL：
Here is the SQL in the stored procedure:
SELECT *
FROM Contacts
LEFT OUTER JOIN Phones ON Phones.ReferenceId=Contacts.ReferenceId
WHERE clientid=1
我尝试了这个，但最终得到了4个元组（这还可以，但不是我想要的...这只是意味着我仍然必须重新标准化结果）：
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);
，当我尝试另一种方法（如下）时，出现了无法转换对象键入'System.Int32'键入'System.Collections.Generic.IEnumerable`1 [Phone]'。
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.
在此先感谢
推荐答案
您没有做错什么，这不是API设计的方式。所有 Query API将始终为每个数据库行返回一个对象。
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.
这里有2个问题：
-
如果我们引入一个内置的映射器来处理您的查询，我们应该丢弃重复的数据。 （Contacts。*在您的查询中重复）
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)"
您可以做的是扩展 GridReader 以允许重新映射：
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 };
);
假设您扩展了GridReader并使用映射器：
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.
这篇关于多映射器创建对象层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
