CODEWITHSUNDEEP
ado.netdapper
David Thielen
David Thielen

Reputation: 32874

How do I get multi-mapping to work in Dapper?

I am trying to do the following with Dapper (and failing).

My POCOs (all code simplified) are:

    public class Company
    {
        public int CompanyId { get; private set; }
        public string CompanyName { get; private set; }
        public Person CompanyAddress { get; private set; }
        public Person Administrator { get; private set; }
}
    public class Person
    {
        public int PersonId { get; private set; }
        public string FirstName { get; private set; }
        public string LastName { get; private set; }
}

In the database the Company table has a FK for CompanyAddress and Administrator which maps to the PersonID PK in the Person table. Based on this and this I think the follwoing is how I want to do this:

public static Company Select(IDbConnection connection, int id)
{
    Trap.trap();
    return connection.Query<Company, Person, Person, Company>("select * from Company left join Person address on Company.CompanyAddress = address.PersonId left join Person admin on Company.Administrator = admin.PersonId where Company.CompanyId = @Id",
        (cmpy, addr, admin) => new { PersonId = id }).FirstOrDefault();
}

But that gives me a compile error on the "new { PersonId = id }". What am I getting wrong?

Upvotes: 0

Views: 138

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460018

You need to provide the SplitOn parameter to specify where the next table/class starts. You also shouldn't create an anonymous type but use a new scope to initialize the Administrator property of Company:

string sql = @"select c.CompanyId,c.CompanyName, c.CompanyAddress,
                      address.PersonId, etc. ....
               from Company c
               left join Person address 
                  on Company.CompanyAddress = address.PersonId 
               left join Person admin 
                  on Company.Administrator = admin.PersonId 
               where Company.CompanyId = @Id";
string splitOn = "PersonId"; // maybe two parameters separated by comma, see comment below the answer
return connection.Query<Company, Person, Person, Company>(sql,
    (Company cmpy, Person addr, Person admin) => { cmpy.Administrator = admin; return cmpy; }
    ,null,null,true,splitOn)
    .FirstOrDefault();

However, i'm not sure if that works already since you have two joins to the same table. So i think you need an alias for all of the duplicate columns like PersonId. But this migt be helpful anyway.

Upvotes: 1

Related Questions