How to map multiple objects from the same table with Dapper.net?

Question

I have two SQL tables: Teams and Members. Each team contains 3 members, in the database the members' ids are stored.

Sample Database

How could I map the Member objects into the Teams using the 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

You need to fix your sql query to have a proper join with the Members Table.

Just change it to

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";

and your dapper code will work as you expect filling the three Member instance of every single Team retrieved.

Notice that when you use multimapping, you need to place the SplitOn elements in the proper place to have Dapper understand your requirement to create three different Member variables.

Version for 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)";