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.

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

Shouldn't your team have a List<Member>?

stuartd
– stuartd

2019-08-14 09:39:57 +00:00
Commented Aug 14, 2019 at 9:39 — stuartd
– stuartd, Commented Aug 14, 2019 at 9:39
This is more a Sql syntax problem than a Dapper one

Steve
– Steve

2019-08-14 09:43:33 +00:00
Commented Aug 14, 2019 at 9:43 — Steve
– Steve, Commented Aug 14, 2019 at 9:43
@stuartd No, it's not a one-to-many relationship.

KDani
– KDani

2019-08-14 09:49:22 +00:00
Commented Aug 14, 2019 at 9:49 — KDani
– KDani, Commented Aug 14, 2019 at 9:49

Steve · Accepted Answer · 2019-08-14 11:24:32Z

3

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

edited Aug 14, 2019 at 11:24

answered Aug 14, 2019 at 9:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

KDani Over a year ago

Does this sql expression works with OleDbConnection too? Because I got a syntax error.

Steve Over a year ago

What database are you using?

KDani Over a year ago

MS Access database

Steve Over a year ago

Probably it is the column titled Name. It is a reserved keyword in MS-Access. Try to use m1.[Name]....,m2.[Name],...,m3.[Name]

stuartd Over a year ago

Paste the query into Access and it should tell you what it doesn't like

|

Collectives™ on Stack Overflow

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

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related