Correct use of multimapping in Dapper

c#, dapper

Solution

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`.

Problem

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: ``` 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 misunderstanding the core functionality of the multimapping feature? I don't want to have to add a complete list of column names each time.

Original source