http://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper/7478958#7478958
I'm trying to use the Multimapping feature of dapper to return a list of ProductItems and associated Customers.
[Table("Product")]
public class ProductItem
{
public decimal ProductID { get; set; }
public string ProductName { get; set; }
public string AccountOpened { get; set; }
public Customer Customer { get; set; }
}
public class Customer
{
public decimal CustomerId { get; set; }
public string CustomerName { get; set; }
}
My dapper code is as follows
var sql = @"select * from Product p
inner join Customer c on p.CustomerId = c.CustomerId
order by p.ProductName";
var data = con.Query<ProductItem, Customer, ProductItem>(
sql,
(productItem, customer) => {
productItem.Customer = customer;
return productItem;
},
splitOn: "CustomerId,CustomerName"
);
This works fine but I seem to have to add the complete column list to the splitOn parameter to return all the customers properties. If I don't add "CustomerName" it returns null. Am I miss-understanding the core functionality of the multimapping feature. I don't want to have to add a complete list of column names each time.
answer
I just ran a test that works fine:
var sql = "select cast(1 as decimal) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as decimal) CustomerId, 'name' CustomerName";
var item = connection.Query<ProductItem, Customer, ProductItem>(sql,
(p, c) => { p.Customer = c; return p; }, splitOn: "CustomerId").First();
item.Customer.CustomerId.IsEqualTo(1);
The splitOn param needs to be specified as the split point, it defaults to Id. If there are multiple split points, you will need to add them in a comma delimited list.
Say your recordset looks like this:
ProductID | ProductName | AccountOpened | CustomerId | CustomerName
--------------------------------------- -------------------------
Dapper needs to know how to split the columns in this order into 2 objects. A cursory look shows that the Customer starts at the column CustomerId, hence splitOn: CustomerId.
There is a big caveat here, if the column ordering in the underlying table is flipped for some reason:
ProductID | ProductName | AccountOpened | CustomerName | CustomerId
--------------------------------------- -------------------------
splitOn: CustomerId will result in a null customer name.
If you specify CustomerId,CustomerName as split points, dapper assumes you are trying to split up the result set into 3 objects. First starts at the beginning, second starts at CustomerId, third at CustomerName.