Mapping multiple complex type properties with Dapper

Question

I have the following Dapper query:

var orderModels = db.Query<OrderModel>(@"
SELECT
    o.[Id],
    o.[CustomerId],

    o.[DeliveryAddress_FirstName],
    o.[DeliveryAddress_LastName],
    o.[DeliveryAddress_Line1],
    o.[DeliveryAddress_Line2],
    o.[DeliveryAddress_City],
    o.[DeliveryAddress_State],
    o.[DeliveryAddress_PostCode],
    o.[DeliveryAddress_Country],

    o.[BillingAddress_FirstName],
    o.[BillingAddress_LastName],
    o.[BillingAddress_Line1],
    o.[BillingAddress_Line2],
    o.[BillingAddress_City],
    o.[BillingAddress_State],
    o.[BillingAddress_PostCode],
    o.[BillingAddress_Country]
FROM
    [Order] o
");

And I'd like to load it into a data model of the following structure:

public class OrderModel
{
    public int Id { get; set; }
    public int CustomerId { get; set; }

    public AddressModel DeliveryAddress { get; set; }
    public AddressModel BillingAddress { get; set; }
}

public class AddressModel
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostCode { get; set; }
    public string Country { get; set; }
}

I have tried to look this up, and there is a feature in Dapper called multi-mapping. However, I can't figure out how to use it in my use-case when I'm not joining results from multiple tables.

It feels like a very common scenario for ORM like Dapper, so I'm sure I'm just missing something obvious. If you're more knowledgeable about Dapper, please help.

What is the best way to accomplish this?

score 4 · Accepted Answer · edited Jan 30 '19 at 11:48

4

Dapper does not care about joins, you just need to provide it correct fields for splitOn options like:

    var orderModels = db.Query<OrderModel, AddressModel, AddressModel, OrderModel>(@"
    SELECT
        o.[Id],
        o.[CustomerId],

        o.[DeliveryAddress_FirstName] AS [FirstName], // Field names should match properties of your model
        o.[DeliveryAddress_LastName] AS [LastName],
        o.[DeliveryAddress_Line1] AS [Line1],
        o.[DeliveryAddress_Line2] AS [Line2],
        o.[DeliveryAddress_City] AS [City],
        o.[DeliveryAddress_State] AS [State],
        o.[DeliveryAddress_PostCode] AS [PostCode],
        o.[DeliveryAddress_Country] AS [Country],

        o.[BillingAddress_FirstName] AS [FirstName],
        o.[BillingAddress_LastName] AS [LastName],
        o.[BillingAddress_Line1] AS [Line1],
        o.[BillingAddress_Line2] AS [Line2],
        o.[BillingAddress_City] AS [City],
        o.[BillingAddress_State] AS [State],
        o.[BillingAddress_PostCode] AS [PostCode],
        o.[BillingAddress_Country] AS [Country]
    FROM
        [Order] o
    ", 
(order, deliveryAddress,  billingAddress) => {
   order.DeliveryAddress = deliveryAddress; 
   order.BillingAddress = billingAddress; 
   return order; 
},
splitOn: "FirstName,FirstName");

It is explained in this article.

Also, the select's field names have to match model property names for Dapper to figure out the mapping automatically.

edited Jan 30 '19 at 11:48

Tom Pažourek

9,582
8
66
107

answered Jan 30 '19 at 09:46

Alex Buyny

3,047
19
25

Thanks for helping. I think you've got the type parameters wrong, it should probably be ``. But even when I do that, billing address and delivery address still have all their properties NULL. – Tom Pažourek Jan 30 '19 at 11:28
yup fixed the parameter order... will have another look at it – Alex Buyny Jan 30 '19 at 11:33
I think the issue is probably that the columns are still prefixed with `DeliveryAddress_`... I tried to alias the columns to remove the prefixes and it worked. But I have to say it feels a bit weird having to rename all these columns (and having multiple columns with the same name). I don't really like having to do a special projection just for Dapper. It would be much nicer if it was possible to configure Dapper as it's Dapper's concern to map the results and it shouldn't make me change my queries. What if these were results generated inside a stored procedure? – Tom Pažourek Jan 30 '19 at 11:39
you can look at [custom column mappings](https://medium.com/dapper-net/custom-columns-mapping-1cd45dfd51d6) but I havent tried those myself – Alex Buyny Jan 30 '19 at 11:42
Thanks, I don't think a custom mapping will help in this case, but it's worth a try. Anyway, the solution with the column aliases works, even when it's not perfect. – Tom Pažourek Jan 30 '19 at 11:48
@TomPažourek you may have a look at this answer, it uses an additional library for doing the mapping, but otherwise it works with columns named exactly like you have them: https://stackoverflow.com/a/30080951/1230302 – Florian Haider Jan 31 '19 at 07:54
@FlorianHaider Thank you for sharing this. I'll have to check this out. – Tom Pažourek Jan 31 '19 at 09:44

Mapping multiple complex type properties with Dapper

1 Answers1