Dapper绑定在多映射中不起作用

Question

这是我的dto.Worker表:

| id | first_name | current_branch |
|----|------------|----------------|
| 1  | Adam       | 5              |

从表中获取数据的方式如下:

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类具有与dto.Worker表相同的结构，但属性不包含_字符:

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

还有一个dto.Job表:

| id | job_name |
|----|----------|
| 1  | foo      |

Anddto.WorkerJobtable:

| worker_id | job_id |
|-----------|--------|
| 1         | 1      |

我想获取所有工人及其工作。我已经确定我需要使用multi mapping dapper feature。我带来了这样的东西:

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), parameters).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属性名称和数据库列名之间存在不匹配。要解决这个问题，我必须更改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;}
}

:

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模型的属性名称?

Answer 1

如果指定每个列和别名，则可以更改查询返回的名称。无论如何，选择"*"都不是最佳实践，因为与显式指定列相比，在服务器中会有一些性能损失。将您的查询更改为:

var sQuery = $@"SELECT worker.id AS Id, 
worker.first_name AS Name,
worker.current_branch AS CurrentBranch,
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";

那将是一个快速修复。但是，您的多映射代码看起来不像标准代码，而且我不知道您需要WorkerJobDTO做什么，毕竟它只是一个链接表。我要把整个东西改成这样:

public async Task<IEnumerable<WorkerJobDTO>> GetAllWorkersJobs()
{
using (SqlConnection connection = connectionFactory.GetConnection())
{
var workerDictionary = new Dictionary<int, WorkerDTO>();
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<WorkerDTO, JobDTO, WorkerDTO>(sQuery, 
(worker, job) =>  
{
WorkerDTO workerEntry;
if (!workerDictionary .TryGetValue(worker.Id, out workerEntry))
{
workerEntry = worker;
workerEntry.Jobs = new List<JobDTO>(); // only if it's not done in the default constructor
workerDictionary.Add(worker.Id, workerEntry);
}
workerEntry.Jobs.Add(job); 
return null; // This doesn't matter, the result will be in workers
}, parameters).ConfigureAwait(false);

}
}

这几乎是Dapper中标准的多映射模式，并且不需要后燃。