映射Dapper结果到嵌套对象

Question

我有一个dapper查询，该查询从db返回一组列：

public async Task<WipCommentCalculation> GetExpenditureCommentsAndData(int projectId, string glPeriodName)
        {
            var output = await _db.LoadData<WipCommentCalculation, dynamic>("dbo.Sp_Get_Expenditure_Comments_Values",
                new { projectId, glPeriodName },
                ConnectionStringName,
                true);

            return output.FirstOrDefault()!;
        }

这是我的loaddata方法：

public async Task<List<T>> LoadData<T, U>(string sqlStatement, U parameters, string connectionStringName, bool isStoredProcedure = false)
        {
            Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

            string connectionString = _config.GetConnectionString(connectionStringName)!;

            CommandType commandType = CommandType.Text;

            if (isStoredProcedure == true)
            {
                commandType = CommandType.StoredProcedure;
            }

            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var rows = await connection.QueryAsync<T>(sqlStatement, parameters, commandType: commandType);
                return rows.ToList();
            }
        }

我的wipcommentCalculation类：

public class WipCommentCalculation
    {
        public Calculations Calculations{ get; set; }

        public CommentModel? Comments { get; set; }

        public int ProjectId{ get; init; }

        public string? ProjectNumber { get; init; }

        public string? GlPeriodName { get; init; }

        public DateTime? LastUpdatedDate { get; set; }

        public string? Status { get; set; }
    }

我的问题是，该映射适用于所有不嵌套对象（status，status，lastupdateddate等） 但是计算和评论模型却是空的。

对象内部的映射是有效的，它们具有正确的名称，在这里获取整个对象我缺少一些东西吗？

例如，我的查询返回在其他列中：

Palaborcomment, 
Panonlaborcomment, 
Pasubcontractorcomment, 
Pmlaborcomment, 
Pmnonlaborcomment, 
Pmsubcontractorcomment

并且我的对象“ commentModel”具有以下定义：

public class CommentModel
{
    [MaxLength (400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PALaborComment { get; set; }

    [MaxLength(400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PANonLaborComment { get; set; }

    [MaxLength(400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PASubContractorComment { get; set; } 

    [MaxLength(400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PMLaborComment { get; set; }

    [MaxLength(400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PMNonLaborComment { get; set; }

    [MaxLength(400, ErrorMessage = "Max length for this field is 400 chars")]
    public string? PMSubcontractorComment { get; set; }
}

SQL StoredProcedure基本上是（简化）：

Select Id, 
    Projectid, 
    Projectnumber, 
    Glperiodname, 
    Palaborcomment, 
    Panonlaborcomment, 
    Pasubcontractorcomment, 
    Pmlaborcomment, 
    Pmnonlaborcomment, 
    Pmsubcontractorcomment, 
    Billablelabor, 
    Billablenonlabor, 
    Billablesubcontractor, 
    Unbilledlabor, 
    Unbillednonlabor, 
    Unbilledsubcontractor, 
    Billingholdlabor, 
    Billingholdnonlabor, 
    Billingholdsubcontractor, 
    Last_Updated_Date, 
    Status
 From Table

和结果行：

+----+-----------+---------------+--------------+----------------+-------------------+------------------------+----------------+-------------------+------------------------+---------------+------------------+-----------------------+---------------+------------------+-----------------------+------------------+---------------------+--------------------------+-------------------------+--------+
| Id | ProjectId | ProjectNumber | GlPeriodName | PALaborComment | PANonLaborComment | PASubContractorComment | PMLaborComment | PMNonLaborComment | PMSubcontractorComment | BillableLabor | BillableNonLabor | BillableSubcontractor | UnbilledLabor | UnbilledNonLabor | UnbilledSubcontractor | BillingHoldLabor | BillingHoldNonLabor | BillingHoldSubcontractor | Last_Updated_Date       | Status |
+====+===========+===============+==============+================+===================+========================+================+===================+========================+===============+==================+=======================+===============+==================+=======================+==================+=====================+==========================+=========================+========+
| 1  | 1622554   | F5Y67802      | MAY-FY2022   | changed        | ewfew             | ewff                   | efewfwe        | ewfew             | NULL                   | 1198780.49    | 153208.27        | 230005.67             | 13141.34      | 394.20           | 0.00                  | 16.31            | 394.20              | 0.00                     | 2022-05-25 10:11:34.510 | NULL   |
+----+-----------+---------------+--------------+----------------+-------------------+------------------------+----------------+-------------------+------------------------+---------------+------------------+-----------------------+---------------+------------------+-----------------------+------------------+---------------------+--------------------------+-------------------------+--------+

我想将所有“ commentmodel”列放入commentmodel对象和计算对象中的所有计算

Answer 1

Dapper无法自动将一个数据库行映射到多个对象。
您需要使用多映射并告诉它在各种类型之间分配的位置。我假设您想要ProjectID。

var projects = new Dictionary<int, WipCommentCalculation>();
var rows = await connection.QueryAsync<WipCommentCalculation, Calculations, CommentModel, WipCommentCalculation>(sqlStatement, parameters, commandType: commandType, splitOn:"Panonlaborcomment,Billablelabor" /* guessing with the last column name*/, (w, co, ca) => 
{
     WipCommentCalculation wip;
     if (!projects.TryGetValue(w.ProjectId, out wip))
     {
         projects.Add(w.ProjectId, wip = w);
     }
     wip.Comments.Add(co);
     wip.Calculations.Add(ca);
     return null;
});
// Use projects here and forget about the return of the query

Answer 2

我假设Dapper在映射EG pmnonlaborcomment到您的表列名称。我建议在每个为您返回nulls的属性上方的每个模型中添加上面的内容：

[Column(Name="column_name")]
public string? PropertyName {get; set;}

尚未要求任何内容，因为您还没有显示您的查询结果，以便我可以看到您正在映射的内容。