c# - 使用多映射 API 时，如果您有 Id“, ”splitOn 以外的键，请确保设置 splitOn 参数与多个关系

Question

当我尝试执行此方法时出现以下异常:

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;
        }

---

---

编辑:

我现在通过像这样更改列的顺序来解决问题:但我想知道是否可以对代码进行更多增强？

 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

您大多误解了 Multimapping 的工作原理使用 Dapper , 您的查询产生以下列:

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

现在在 multimapping代码，下面是小巧玲珑的Query您正在调用的重载(从 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)

早些时候我误解了整个调用，但现在看来，问题仅在于使用 SplitOn 的多映射类型映射，您已经更正了这一点，因此只要正确spliton查询结果中有可用的列

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")

现在只有一点有待澄清，因为我已经在评论中发布了正在运行的 years参数，主要是 integer array , 对于当前全是文本的查询，这可以正常工作，但不适用于 stored procedures ，它期望使用 DataTable 的地方相同, 具有与集合相同的列序列和名称只能使用 Table Valued Parameters 提供.我预计当前用例不需要任何更多更改。

编辑以提供匿名参数和动态参数的示例:

匿名参数

这个简单的 C# 匿名类型，检查 here ，想法是您可以使用像 {max = <value>,min=<value>} 这样的简单占位符来提供所有参数或者即使名称匹配 {max,min} 也是如此, 在这两种情况下参数都是 @max和 @min ，大小写无关紧要，您使用的是 AnonymousParameters在您的参数代码中 years, PeriodTypeId, Period , 它将在内部推断出类型和其他细节，并假设所有这些都是输入参数

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

动态参数

它们更像是 Ado.Net 中的参数类，它让你显式地添加一个参数，下面是 Dapper 代码中的重载，你必须提供所有信息，如 Type, Direction等等(来自 dapper 源代码的代码片段):

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)
    }