Dapper Multi Mapping Result

Question

I have two classes

public class Customer
{
    public int CustomerId { get; set;}
    public string CustomerName { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }       //BuyerCustomer
    public int CustomerSecondId { get; set; } //ReceiverCustomer
    public Customer BuyerCustomer { get; set; }
    public Customer ReceiverCustomer { get; set; }
}

Here's my query will look like

SELECT a.*, b.*, c.* FROM dbo.PRODUCTS_ORDER a
INNER JOIN  dbo.CUSTOMER b ON a.CustomerId=b.CustomerId
INNER JOIN   dbo.CUSTOMER c ON a.CustomerSecondId=b.CustomerId

Dapper Implementation..

     List<Order> order= null;
 order= (List<Order>)dapperconnection.Query<Order, Customer, Customer, Order>(sql,
                                (order, customer1,customer2) =>
                                {
                                    order.BuyerCustomer = customer1;
                                    order.ReceiverCustomer = customer2;
                                    return order;
                                }, splitOn: "CustomerId,CustomerSecondId ");

The result I'm getting is incomplete, only the RecevierCustomer gets populated while the BuyerCustomer doesn't contain any values at all.

It looks like dapper is confused since i used the CustomerId twice in my query. Is there any workaround with this without having to change my the Customer class?

Answer 1

There are few issues with your class design and Dapper query.

• Customer.CustomerName should be string
• I would remove CustomerId and CustomerSecondId from Order. They are redundant. You have both Id's in the Customer.
• Remove CustomerSecondId from Split.

Below is a working test:

public class Order
{
    public int OrderId { get; set; }
    public Customer BuyerCustomer { get; set; }
    public Customer ReceiverCustomer { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}


[Test]
public void TestSplitMany()
{
    using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
    {
        var result =
            conn.Query<Order, Customer, Customer, Order>(@"select OrderId = 1, CustomerId = 2, CustomerName = 'Jim', CustomerId = 3, CustomerName = 'Bob'",
                (order, buyer, receiver) =>
                {
                    order.BuyerCustomer = buyer;
                    order.ReceiverCustomer = receiver;
                    return order;
                }, splitOn: "CustomerId").First();

        Assert.That(result.BuyerCustomer.CustomerId == 2);
        Assert.That(result.ReceiverCustomer.CustomerId == 3);

        Assert.That(result.BuyerCustomer.CustomerName == "Jim");
        Assert.That(result.ReceiverCustomer.CustomerName == "Bob");
    }
}