When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id“, ”splitOn with Multiple relations

Question

I get the following exception when i try to execute this method :

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id“

---

public static IEnumerable<FinancePositionList> GetFinancialListsForConsecutiveYears(int year, int periodTypeId, int period)
        {
            IEnumerable<FinancePositionList> resultList;
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Finance"].ConnectionString))
            {
                conn.Open();
                StringBuilder query = new StringBuilder();
                query.Append("SELECT b.CompanyId,b.CompanyName,[Year]");
                query.Append(",CreationDate,AccruedExpenses,AdvancePaymentsToContractors");


                query.Append("FROM finance.FinanceList a INNER JOIN finance.Company b ");
                query.Append("ON a.CompanyId = b.CompanyId ");
                query.Append("INNER JOIN finance.ListPeriod c ");
                query.Append("ON c.FinanceListId = a.FinanceListId ");
                query.Append("WHERE Discriminator = " + "'" + "FinancePositionList" + "' ");
                query.Append("AND[Year] IN @years ");
                query.Append("AND c.PeriodTypeId = @PeriodTypeId AND c.Period = @Period ");
                query.Append("ORDER BY b.CompanyId, a.[Year] DESC ");

                resultList = conn.Query<FinancePositionList, Company,ListPeriod, FinancePositionList>(query.ToString(),
                   (a, b,c) =>
                   {
                       a.Company = b;
                       c.FinanceList = a;
                       return a;
                   },
                  new
                  {
                      years = new[] { year, year - 1 },
                      PeriodTypeId = periodTypeId,
                      Period = period
                  },
                    splitOn: "CompanyId,FinanceListId").AsEnumerable();
            }
            return resultList;
        }

---

---

EDIT :

I fix the problem now by changing the order of columns like this : But I wonder if there are more enhancement to the code could i do ?

 public static IEnumerable<FinancePositionList> GetFinancialListsForConsecutiveYears(int year, int periodTypeId, int period)
        {
            IEnumerable<FinancePositionList> resultList;
            using (var conn = new SqlConnection(ResolveConnectionString()))
            {
                conn.Open();
                StringBuilder query = new StringBuilder();
                query.Append("SELECT CreationDate,AccruedExpenses,AdvancePaymentsToContractors,[Year]");
                query.Append(",b.CompanyId,b.CompanyName,c.FinanceListId ");
                query.Append("FROM finance.FinanceList a INNER JOIN finance.Company b ");
                query.Append("ON a.CompanyId = b.CompanyId ");
                query.Append("INNER JOIN finance.ListPeriod c ");
                query.Append("ON c.FinanceListId = a.FinanceListId ");
                query.Append("WHERE Discriminator = " + "'" + "FinancePositionList" + "' ");
                query.Append("AND [Year] IN @years ");
                query.Append("AND c.PeriodTypeId = @PeriodTypeId AND c.Period = @Period ");
                query.Append("ORDER BY b.CompanyId, a.[Year] DESC ");

                resultList = conn.Query<FinancePositionList, Company, ListPeriod, FinancePositionList>(query.ToString(),
                   (a, b, c) =>
                   {
                       a.Company = b;
                       a.CompanyId = b.CompanyId;
                       a.FinanceListId = c.FinanceListId;
                       return a;
                   },
                  new
                  {
                      years = new[] { year, year - 1 },
                      PeriodTypeId = periodTypeId,
                      Period = period
                  },
                    splitOn: "CompanyId,FinanceListId").AsEnumerable();
            }
            return resultList;
        }

Answer 1

You have mostly misunderstood the working of the Multimapping using Dapper, Your query yields the following columns:

**"CompanyId","CompanyName","Year","CreationDate","AccruedExpenses",
"AdvancePaymentsToContractors"**

Now the in the multimapping code, following is the dapper Query overload that you are calling (checked from the source code):

public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(
this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TReturn> map, 
object param, IDbTransaction transaction, bool buffered, string splitOn, 
int? commandTimeout, CommandType? commandType)

Earlier I misunderstood the overall call, but now as it seems, issue is only with the Multi mapping type mapping using SplitOn, which you have already corrected, therefore the following would work, provided the correct spliton columns are available in the query result

conn.Query<FinancePositionList, Company,ListPeriod, FinancePositionList>(query.ToString(), (a, b,c) =>
                   {
                       a.Company = b;
                       c.FinanceList = a;
                       return a;
                   },
                  new
                 {
                    years = new[] { year, year - 1 },
                    PeriodTypeId = periodTypeId,
                    Period = period
                 }, splitOn: "CompanyId,FinanceListId")

Now only point remains to be clarified as I have posted in the comment is working of years parameter, which mostly an integer array, for the current query which is all text, this would work fine, but not for the stored procedures, where it would expect the same using DataTable, with same sequence and name of the columns as the collection can be supplied only using Table Valued Parameters. I don't foresee any more change required to the current use case.

Edit to provide an example of Anonymous Parameters and Dynamic Parameters:

AnonymousParameters

This simple anonymous type in C#, check here, idea is you can supply all the parameters using simple placeholder like {max = <value>,min=<value>} or even as is if the name match {max,min}, in both cases parameters are@max and @min , case doesn't matter, you are using the the AnonymousParameters in your code for the parameters years, PeriodTypeId, Period, it will deduce the type and other details internally and would assume all to be input parameters

{years = new[] { year, year - 1 },PeriodTypeId = periodTypeId,Period = period}

Dynamic Parameters

They are more like Parameter class in Ado.Net, which gets you to add a parameter explicitly, following are the overloads in the Dapper code, you have to supply all the information like Type, Direction etc explicitly (code snippet from dapper source code):

public partial class DynamicParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup, SqlMapper.IParameterCallbacks
    {
    public void Add(string name, object value, DbType? dbType, ParameterDirection? direction, int? size)

    public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null, byte? precision = null, byte? scale = null)
    }