Dapper map with prefix in column name

2
votes

I have 2 classes, Order and Address as following:

public class Order
{
    public string OrderId { get; set; }
    public Address ShippingAddress { get; set; }
    public Address BillingAddress { get; set; }
}

and 

public class Address
{
    public string Street { get; set; }
    public string Town { get; set; }
    public string Zip { get; set; }
}

The database stores the orders and address in a single table like this: 

CREATE TABLE Orders
    (
        OrderId NVARCHAR(56) PRIMARY KEY,
        BillingStreet NVARCHAR(256),
        BillingTown NVARCHAR(256),
        BillingZip NVARCHAR(256),
        ShippingStreet NVARCHAR(256),
        ShippingTown NVARCHAR(256),
        ShippingZip NVARCHAR(256)
    )

enter image description here

How can i get dapper to map this to the Order class?

2
c#dapper

2 Answers

3
votes

Here's how you can do that by making the query generalize the billing and shipping columns and using the version of Query that takes multiple types and telling it to split when it sees a column called "Address". Then you just assign the Address objects to the appropriate property on the Order object.

connection.Query<Order, Address, Address, Order>(
    @"SELECT OrderId, 
             BillingAddress As Address, 
             BillingTown As Town, 
             BillingZip As Zip, 
             ShippingAddress As Address, 
             ShippingTown As Town, 
             ShippingZip As Zip,  
      FROM Orders",
    (o, ba, sa) =>
    {
        o.BillingAddress = ba;
        o.ShippingAddress = sa;
        return o;
    },
    splitOn: "Address");
0
votes

I don't think it is possible with Dapper since it treats row as a single object. It would be possible if you would change your table structure:

CREATE TABLE Orders
(
    OrderId NVARCHAR(56) PRIMARY KEY,
    BillingAddressId INT
    ShippingAddressId INT
)

Then you would have to change your class to:

public class Order
{
    public string OrderId { get; set; }
    public int ShippingAddressId {get; set;}
    public virtual Address ShippingAddress { get; set; }
    public int BillingAddressId {get; set;}
    public virtual Address BillingAddress { get; set; }
}

And just use multi mapping.

Another option is to use Dapper extensions like Dapper-FluentMap or Dapper Extensions which will help you map columns to classes.