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?

Void Ray · Accepted Answer · 2017-07-26 20:45:20Z

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"); } }

Oh, sorry for the typo. I fixed it. Let me try to do run some test. Thanks.
Why do i need to remove the Remove CustomerSecondId from Split? I honestly don't get the idea since i really need it for the joins.
CustomerSecondId is part of the order. When you look at the SQL query result, you have Order followed by Customer, Customer, then we just divide two customer records by CustomerId. I'll edit last point.

Collectives™ on Stack Overflow

Dapper Multi Mapping Result

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related