如何使用Dapper自动将关联查询结果映射到嵌套实体类集合？

我有如下实体类：

public class AssessmentModel
{
    public Guid assessmentId { get; set; }
    public string? applicationNumber { get; set; }
    public ApplicationStatus? applicationStatus { get; set; }
    public List<CompanyInformationModel>? companyInformationModel { get; set; }
    public List<LocationInformationModel>? locationInformationModels { get; set; }
    public List<JobInformationModel>? jobInformationModel { get; set; }
}

public class CompanyInformationModel
{
    public Guid CompanyInformationId { get; set; }
    public string? companyName { get; set; }
    public string? contactName { get; set; }
    public string? primaryPhone { get; set; }
    public string? secondaryPhone { get; set; }
    public string? email { get; set; }
    public string? hourlyRate { get; set; }
}

public class LocationInformationModel
{
    public Guid LocationInformationId { get; set; }
    public string? address1 { get; set; }
    public string? address2 { get; set; }
    public string? city { get; set; }
    public string? state { get; set; }
    public string? zip { get; set; }
}

public class JobInformationModel
{
    Guid JobInformationId { get; set; }
    string? jobTitle { get; set; }
    string? jobDescription { get; set; }
}

public enum ApplicationStatus
{
    [Description("Pending")]
    Pending,
    [Description("Approved")]
    Approved,
    [Description("Declined")]
    Declined
}

我使用Dapper执行如下关联查询：

var sqlStatement = $@"Select * from SituationalAssessment s 
                      join CompanyInformation c  on c.CompanyInformationId = s.AssessmentId
                      join LocationInformation l  on l.LocationInformationId = s.AssessmentId
                      join JobInformation j  on j.JobInformationId = s.AssessmentId";
var retVal= await _dapperHelper.ExecuteQueryAsync<AssessmentModel>(sqlStatement);

查询结果如下：

AssessmentId    ApplicationNumber   ApplicationStatus   DateCreated ModifiedDateCreated CompanyInformationId    CompanyName ContactName PrimaryPhone    SecondaryPhone  Email   HourlyRate  LocationInformationId   Address1    Address2    City    State   Zip JobInformationId    JobTitle    JobDescription
78FE1852-5A26-4624-925C-74653DE9DAD9    227114  Pending 2023-02-08 13:57:52.743 NULL    78FE1852-5A26-4624-925C-74653DE9DAD9    Company Test    Test Contact    123-234-2343    NULL    test1@email.com NULL    78FE1852-5A26-4624-925C-74653DE9DAD9    123 Main Ste    Apt 201 Los Angeles CA  90210   78FE1852-5A26-4624-925C-74653DE9DAD9    TEst    Test Description

数据库列名与类属性名完全一致，请问如何通过Dapper自动将CompanyInformation、LocationInformation和JobInformation的查询结果映射到各自对应的实体类集合中？

Dapper默认不会自动映射集合类型属性，需要结合**多映射（Multi-Mapping）**和分组逻辑处理这种一对多关联场景，以下是具体实现：

1. 修正实体类访问修饰符 #

首先修复JobInformationModel的属性访问权限，Dapper无法映射私有属性，需添加public修饰符：

public class JobInformationModel
{
    public Guid JobInformationId { get; set; }
    public string? jobTitle { get; set; }
    public string? jobDescription { get; set; }
}

2. 多映射+字典分组实现关联映射 #

放弃封装的ExecuteQueryAsync<T>，直接使用Dapper原生QueryAsync方法，通过委托定义映射规则并分组去重：

var sqlStatement = @"Select * from SituationalAssessment s 
                     join CompanyInformation c on c.CompanyInformationId = s.AssessmentId
                     join LocationInformation l on l.LocationInformationId = s.AssessmentId
                     join JobInformation j on j.JobInformationId = s.AssessmentId";

var assessmentDict = new Dictionary<Guid, AssessmentModel>();

await _dapperHelper.Connection.QueryAsync<AssessmentModel, CompanyInformationModel, LocationInformationModel, JobInformationModel, AssessmentModel>(
    sqlStatement,
    (assessment, company, location, job) =>
    {
        // 用AssessmentId作为键去重，避免重复创建主实体
        if (!assessmentDict.TryGetValue(assessment.assessmentId, out var existingAssessment))
        {
            existingAssessment = assessment;
            existingAssessment.companyInformationModel = new List<CompanyInformationModel>();
            existingAssessment.locationInformationModels = new List<LocationInformationModel>();
            existingAssessment.jobInformationModel = new List<JobInformationModel>();
            assessmentDict.Add(existingAssessment.assessmentId, existingAssessment);
        }

        // 将子实体添加到对应集合
        existingAssessment.companyInformationModel.Add(company);
        existingAssessment.locationInformationModels.Add(location);
        existingAssessment.jobInformationModel.Add(job);

        return existingAssessment;
    },
    splitOn: "CompanyInformationId,LocationInformationId,JobInformationId"
);

// 最终结果为去重后的主实体集合
var retVal = assessmentDict.Values.ToList();

关键说明： #

• splitOn参数：指定Dapper拆分结果集的列名，告诉Dapper从哪一列开始切换到下一个实体类的映射，这里对应三个子实体的主键列。
• 字典分组：关联查询会产生笛卡尔积（主实体重复出现），用字典以AssessmentId为键去重，确保每个主实体仅创建一次，再将子实体批量添加到对应集合。

3. 优化查询（避免笛卡尔积） #

如果每个主实体对应多个子实体，JOIN查询会产生大量重复数据，可改为分表查询后手动关联，提升性能：

// 查询主实体
var assessments = await _dapperHelper.Connection.QueryAsync<AssessmentModel>(
    "SELECT * FROM SituationalAssessment");

var assessmentIds = assessments.Select(a => a.assessmentId).ToList();

// 查询并关联公司信息
var companyInfos = await _dapperHelper.Connection.QueryAsync<CompanyInformationModel>(
    "SELECT * FROM CompanyInformation WHERE CompanyInformationId IN @Ids",
    new { Ids = assessmentIds });
foreach (var assessment in assessments)
{
    assessment.companyInformationModel = companyInfos
        .Where(c => c.CompanyInformationId == assessment.assessmentId)
        .ToList();
}

// 查询并关联位置信息
var locationInfos = await _dapperHelper.Connection.QueryAsync<LocationInformationModel>(
    "SELECT * FROM LocationInformation WHERE LocationInformationId IN @Ids",
    new { Ids = assessmentIds });
foreach (var assessment in assessments)
{
    assessment.locationInformationModels = locationInfos
        .Where(l => l.LocationInformationId == assessment.assessmentId)
        .ToList();
}

// 查询并关联职位信息
var jobInfos = await _dapperHelper.Connection.QueryAsync<JobInformationModel>(
    "SELECT * FROM JobInformation WHERE JobInformationId IN @Ids",
    new { Ids = assessmentIds });
foreach (var assessment in assessments)
{
    assessment.jobInformationModel = jobInfos
        .Where(j => j.JobInformationId == assessment.assessmentId)
        .ToList();
}

var retVal = assessments;

这种方式避免了数据冗余，适合子实体数量较多的场景。

内容的提问来源于stack exchange，提问作者CodeMan03