如何使用 Dapper.net 映射同一表中的多个对象?

Question

我有两个SQL表：团队和成员。每个团队包含 3 名成员，成员的 ID 存储在数据库中。

示例数据库

如何使用 Dapper.NET ORM 将成员对象映射到团队中？

public class Team
{
public int? id { get; set; }
public Member MemberA { get; set; }
public Member MemberB { get; set; }
public Member MemberC { get; set; }
}
public class Member
{
public int? id { get; set; }
public string Name { get; set; }
}

public IEnumerable<Team> GetTeams()
{
string sql = "SELECT * FROM Teams t LEFT JOIN Members m ON t.MemberA=m.id AND t.MemberB=m.id AND t.MemberC=m.id";
return m_connection.Query<Team, Member, Member, Member, Team>(sql, (t, m1, m2, m3) =>
{
t.MemberA = m1;
t.MemberB = m2;
t.MemberC = m3;
return t;
}, splitOn: "MemberA,MemberB,MemberC");
}

Answer 1

您需要修复 sql 查询以与成员表进行正确的连接。

只需将其更改为

string sql = @"SELECT t.ID, t.MemberA, m1.Id, m1.Name, 
t.MemberB, m2.Id, m2.Name, 
t.MemberC, m3.Id, m3.Name
FROM Teams t LEFT JOIN Members m1 ON t.MemberA=m1.id 
LEFT JOIN Members m2 ON t.MemberB=m2.id
LEFT JOIN Members m3 ON t.MemberC=m3.id";

并且您的 dapper 代码将按您的预期工作，填充检索到的每个团队的三个成员实例。

请注意，使用多重映射时，需要将 SplitOn 元素放置在适当的位置，以使 Dapper 了解创建三个不同成员变量的要求。

MS-Access 版本

string sql = @"SELECT t.ID, t.MemberA, m1.Id, m1.[Name], 
t.MemberB, m2.Id, m2.[Name], 
t.MemberC, m3.Id, m3.[Name]
FROM (((Teams t LEFT JOIN Members m1 ON t.MemberA=m1.id) 
LEFT JOIN Members m2 ON t.MemberB=m2.id)
LEFT JOIN Members m3 ON t.MemberC=m3.id)";