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?

Answer 1

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.