Dapper: Cannot use multi mapping with two properties of the same type

[batwad]

Let's say I have contacts stored in my database in a flattened form, such that I query them like this:

SELECT Name, HomeHouseNumber, HomePostcode, WorkHouseNumber, WorkPostcode FROM Contacts

I would like a little more structure in my C# code and have this simple definition of a contact with a home and work address.

class Address
{
    public string HouseNumber { get; set; }
    public string Postcode { get; set; }
}

class Contact
{
    public string Name { get; set; }
    public Address HomeAddress { get; set; }
    public Address WorkAddress { get; set; }
}

I've found I can use multi mapping do extract the home address by aliasing the columns in the select like this:

IEnumerable<Contact> GetContacts()
{
    return Connection.Query<Contact, Address, Address, Contact>(
        "SELECT Name, HomeHouseNumber as HouseNumber, HomePostcode as Postcode, WorkHouseNumber, WorkPostcode FROM Contacts",
        (contact, home, work) =>
        {
            contact.HomeAddress = home;
            contact.WorkAddress = work; // work does not get mapped
            return contact;
        },
        splitOn: "HouseNumber,WorkHouseNumber");
}

However I cannot alias the work address columns in such a way that they will be mapped. Can Dapper perform this mapping for me or must I do it manually?

[markjerz]

I think you need to use return Connection.Query<Contact, Address, Address, Contact>

[batwad]

You're right, sorry that was a typo in my example. The issue remains however.

[NickCraver]

Dapper maps via field name, so you would need to match the columns on the 2nd Address object to your C# object, it doesn't know what your column names are intended for - position is not what is used (since properties could come back in any order, for example). Try this:

c# IEnumerable<Contact> GetContacts() { return Connection.Query<Contact, Address, Address, Contact>( "SELECT Name, HomeHouseNumber as HouseNumber, HomePostcode as Postcode, WorkHouseNumber as HouseNumber, WorkPostcode as Postcode FROM Contacts", (contact, home, work) => { contact.HomeAddress = home; contact.WorkAddress = work; return contact; }, splitOn: "HouseNumber,HouseNumber"); }

[batwad]

ohmigod this works! I didn't think to try this because I though SQL Server would never in a million years let you give two columns the same alias. Turns out you can!

I can't help but be nervous that it magically gets home and work the right way around, without anything to explicitly identify them as such. I guess it relies on splitOn working left-to-right?

[NickCraver]

You're defining the order, here: (contact, home, work), it's mapping objects of Contact, Address, Address, and you're saying which order to use them in - it's all safe :)

[batwad]

All hail Dapper!