Correct use of Multimapping in Dapper

dapper

Question

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.

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


Popular Answer

Our tables are named similarly to yours, where something like "CustomerID" might be returned twice using a 'select *' operation. Therefore, Dapper is doing its job but just splitting too early (possibly), because the columns would be:

(select * might return):
ProductID,
ProductName,
CustomerID, --first CustomerID
AccountOpened,
CustomerID, --second CustomerID,
CustomerName.

This makes the spliton: parameter not so useful, especially when you're not sure what order the columns are returned in. Of course you could manually specify columns...but it's 2017 and we just rarely do that anymore for basic object gets.

What we do, and it's worked great for thousands of queries for many many years, is simply use an alias for Id, and never specify spliton (using Dapper's default 'Id').

select 
p.*,

c.CustomerID AS Id,
c.*

...voila! Dapper will only split on Id by default, and that Id occurs before all the Customer columns. Of course it will add an extra column to your return resultset, but that is extremely minimal overhead for the added utility of knowing exactly which columns belong to what object. And you can easily expand this. Need address and country information?

select
p.*,

c.CustomerID AS Id,
c.*,

address.AddressID AS Id,
address.*,

country.CountryID AS Id,
country.*

Best of all, you're clearly showing in a minimal amount of sql which columns are associated with which object. Dapper does the rest.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why