Dapper Multi-mapping Issue

Question

Keep running into "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id" error for the below code-block:

var accounts = DbConnection.Query<Account, Branch, Application, Account>(
            "select Accounts.*, SplitAccount = '', Branches.*, SplitBranch = '', Applications.*" +
            " from Accounts" +
            "    join Branches" +
            "       on Accounts.BranchId = Branches.BranchId" +
            "    join Applications" +
            "       on Accounts.ApplicationId = Applications.ApplicationId" +
            " where Accounts.AccountId <> 0",
            (account, branch, application) =>
                {
                    account.Branch = branch;
                    account.Application = application;
                    return account;
                }, splitOn : "SplitAccount, SplitBranch"
            ).AsQueryable();

I'm using SplitAccount and SplitBranch for splitOn as a workaround.

Em I missing something?

Thanks

Edit:

I have cleaned up my test a little, below is a light version of classes and a new query:

public class AccountLight
{
    public int AccountId { get; set; }
    public string AccountNumber { get; set; }
    public BranchLight Branch { get; set; }
    public ApplicationLight Application { get; set; }
}

public class BranchLight
{
    public int BranchId { get; set; }
    public string BranchNumber { get; set; }
}

public class ApplicationLight
{
    public int ApplicationId { get; set; }
    public string ApplicationCode { get; set; }
}

var accounts2 = DbConnection.Query<AccountLight, BranchLight, ApplicationLight, AccountLight>(
    "select Accounts.AccountId, Accounts.AccountNumber," +
    "       Branches.BranchId, Branches.BranchNumber," +
    "       Applications.ApplicationId, Applications.ApplicationCode" +
    " from Accounts" +
    "    inner join Branches" +
    "       on Accounts.BranchId = Branches.BranchId" +
    "    inner join Applications" +
    "       on Accounts.ApplicationId = Applications.ApplicationId" +
    " where Accounts.AccountId <> 0",
    (account, brach, application) =>
    {
        account.Branch = brach;
        account.Application = application;
        return account;
    }, 
    commandType: CommandType.Text,
    splitOn: "AccountId, BranchId"
    ).AsQueryable();

Answer 1

After few hours of debugging Dapper's source code, I finally found the issue and it is quite interesting one.

When multiple splitOn fields are supplied, Dapper does a split based on comma, e.g. var splits = splitOn.Split(',').ToArray(). Then it loops through all record-set fields and split’s them up into objects based on the above array; pretty strait forward.

Now the fun part: When I supplied my splitOn fields, I had an extra SPACE after the comma, e.g. “AccountId, BranchId” and that little space was the cause. After Split(), BranchId field contained an extra space and failed to match with ANY fields in the record-set.

There are two ways around this:

1. Do not use extra spaces after commas; which I personally addicted to; an old habit from SQL.
2. Modify Dapper’s GenerateDeserializers method and change: var currentSplit = splits[splitIndex] to var currentSplit = splits[splitIndex].Trim(), or something similar; that is what I did for my local copy.

Here is code snapshot:

    private static Func<IDataReader, object>[] GenerateDeserializers(Type[] types, string splitOn, IDataReader reader)
    {
        int current = 0;
        var splits = splitOn.Split(',').ToArray();
        var splitIndex = 0;

        Func<Type, int> nextSplit = type =>
        {
            var currentSplit = splits[splitIndex].Trim();
            if (splits.Length > splitIndex + 1)
            {
                splitIndex++;
            }

Update:

The above fix got merged: https://github.com/SamSaffron/dapper-dot-net/commit/399db17e5aa6f1eefaf8fdccff827020be8e6cbb