Dapper多映射绑定失效，如何不修改DTO属性名解决？

数据库表结构 #

dto.Worker表 #

dto.Job表 #

dto.WorkerJob表 #

单表查询正常实现 #

单表查询时，可直接将dto.Worker数据映射到驼峰命名的WorkerDTO：

查询代码：

public async Task<WorkerDTO> GetById(int workerId)
{
    using (var connection = connectionFactory.GetConnection())
    {
        string sQuery = $@"SELECT * FROM dto.Worker WITH(NOLOCK) WHERE id = @WorkerId";
        return await connection.QueryFirstAsync<WorkerDTO>(sQuery, new { WorkerId = workerId }).ConfigureAwait(false);
    }
}

WorkerDTO结构：

public class WorkerDTO
{
    public int Id {get; set;}
    public string FirstName {get; set;}
    public int CurrentBranch {get; set;}
}

多映射查询遇到的问题 #

使用Dapper多映射查询工人及其关联工作时，驼峰命名的WorkerJobDTO无法与数据库下划线列名绑定，仅修改DTO属性名为下划线格式才能生效。

多映射查询代码：

public async Task<IEnumerable<WorkerJobDTO>> GetAllWorkersJobs()
{
    using (SqlConnection connection = connectionFactory.GetConnection())
    {
        var sQuery = $@"SELECT worker.*, job.* FROM dbo.Worker worker
                LEFT JOIN dbo.WorkerJob workerJob ON workerJob.worker_id = worker.id
                LEFT JOIN dbo.Job job ON job.id = workerJob.job_id";
    
        var workers = await connection.QueryAsync<WorkerJobDTO, JobDTO, (WorkerJobDTO workerJob, JobDTO job)>(sQuery, (worker, job) => (worker, job)).ConfigureAwait(false);
    
        return workers.GroupBy(x => x.worker.Id)
            .Select(group =>
            {
                var worker = group.First().worker;
                worker.Jobs = group
                    .Select(x => x.job)
                    .Where(x => x != null)
                    .ToList();
                return worker;
            });
    }
}

初始WorkerJobDTO结构（绑定失效）：

public class WorkerJobDTO
{
    public int Id {get; set;}
    public string FirstName {get; set;}
    public int CurrentBranch {get; set;}
    public List<JobDTO> Jobs {get; set;}
}

修改后的WorkerJobDTO（绑定生效，但不符合驼峰规范）：

public class WorkerJobDTO
{
    public int id {get; set;}
    public string first_name {get; set;}
    public int current_branch {get; set;}
    public List<JobDTO> Jobs {get; set;}
}

无需修改DTO属性名的解决办法 #

方法1：SQL查询显式指定列别名 #

在SQL语句中为下划线列名指定对应驼峰属性的别名，让Dapper直接匹配：

SELECT 
    worker.id AS Id,
    worker.first_name AS FirstName,
    worker.current_branch AS CurrentBranch,
    job.id AS Id,
    job.job_name AS JobName
FROM dto.Worker worker
LEFT JOIN dto.WorkerJob workerJob ON workerJob.worker_id = worker.id
LEFT JOIN dto.Job job ON job.id = workerJob.job_id

注意：多映射时需通过splitOn参数指定拆分不同实体的列，修改后的查询代码：

var workers = await connection.QueryAsync<WorkerJobDTO, JobDTO, (WorkerJobDTO workerJob, JobDTO job)>(
    sQuery, 
    (worker, job) => (worker, job), 
    splitOn: "Id"
).ConfigureAwait(false);

方法2：配置Dapper全局下划线转驼峰映射 #

在项目启动时添加全局配置，开启Dapper自动将下划线列名映射到驼峰属性的功能：

// 放在Startup.cs或Program.cs等启动类中
Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

配置后，所有Dapper查询都会自动处理下划线到驼峰的转换，无需修改SQL或DTO。

方法3：使用特性标注列名映射 #

在WorkerJobDTO的属性上添加[Column]特性，指定对应的数据库列名（需引用System.ComponentModel.DataAnnotations或Dapper.Contrib.Extensions）：

using System.ComponentModel.DataAnnotations.Schema;

public class WorkerJobDTO
{
    [Column("id")]
    public int Id {get; set;}
    [Column("first_name")]
    public string FirstName {get; set;}
    [Column("current_branch")]
    public int CurrentBranch {get; set;}
    public List<JobDTO> Jobs {get; set;}
}

此方法适合需要对个别属性做特殊映射的场景。

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