Correct use of multimapping in Dapper

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.

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.

If you need to map a large entity write each field must be a hard task.

I tried @BlackjacketMack answer, but one of my tables has an Id Column other ones not (I know it's a DB design problem, but ...) then this insert an extra split on dapper, that's why

select
p.*,

c.CustomerID AS Id,
c.*,

address.AddressID AS Id,
address.*,

country.CountryID AS Id,
country.*

Doesn't work for me. Then I ended with a little change to this, just insert an split point with a name that doesn't match with any field on tables, In may case changed as Id by as _SplitPoint_, the final sql script looks like this:

select
p.*,

c.CustomerID AS _SplitPoint_,
c.*,

address.AddressID AS _SplitPoint_,
address.*,

country.CountryID AS _SplitPoint_,
country.*

Then in dapper add just one splitOn as this

cmd =
    "SELECT Materials.*, " +
    "   Product.ItemtId as _SplitPoint_," +
    "   Product.*, " +
    "   MeasureUnit.IntIdUM as _SplitPoint_, " +
    "   MeasureUnit.* " +
    "FROM   Materials INNER JOIN " +
    "   Product ON Materials.ItemtId = Product.ItemtId INNER JOIN " +
    "   MeasureUnit ON Materials.IntIdUM = MeasureUnit.IntIdUM " +
List < Materials> fTecnica3 = (await dpCx.QueryAsync<Materials>(
        cmd,
        new[] { typeof(Materials), typeof(Product), typeof(MeasureUnit) },
        (objects) =>
        {
            Materials mat = (Materials)objects[0];
            mat.Product = (Product)objects[1];
            mat.MeasureUnit = (MeasureUnit)objects[2];
            return mat;
        },
        splitOn: "_SplitPoint_"
    )).ToList();

Assuming the following structure where '|' is the point of splitting and Ts are the entities to which the mapping should be applied.

       TFirst         TSecond         TThird           TFourth
------------------+-------------+-------------------+------------
col_1 col_2 col_3 | col_n col_m | col_A col_B col_C | col_9 col_8
------------------+-------------+-------------------+------------

Following is the Dapper query that you will have to write.

Query<TFirst, TSecond, TThird, TFourth, TResut> (
    sql : query,
    map: Func<TFirst, TSecond, TThird, TFourth, TResut> func,
    parma: optional,
    splitOn: "col_3, col_n, col_A, col_9")

So we want for TFirst to map to col_1 col_2 col_3, for TSecond to col_n col_m ...

The splitOn expression translates to:

Start mapping of all columns into TFirst till you find a column named or aliased as 'col_3', and also include 'col_3' into the mapping result.

Then start mapping into TSecond all columns starting from 'col_n' and continue mapping till new separator is found, which in this case is 'col_A', and mark the start of TThird mapping and so on.

The columns of the SQL query and the props of the mapping object are in a 1:1 relation (meaning that they should be named the same). If the column names resulting from the SQL query are different, you can alias them using the 'AS [Some_Alias_Name]' expression.