Dapper binding doesnt work in multi mapping

Question

TThis is my dto.Worker table:

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

This is how I fetch data from this table:

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

The WorkerDTO class has the same structure as dto.Worker table, but properties doesn't contain _ character:

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

There is also a dto.Job table:

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

And dto.WorkerJob table:

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

I want to fetch all workers and its jobs. I already established that I need to use multi mapping dapper feature. I came with something like this:

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

Unfortunately this doesn't work, because there is a mismatch between WorkerJobDTO properties names and database column names. To fix this, I have to change WorkerJobDTO class from:

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

to:

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

Is there any way to fix this without modyfing DTO model's properties names?

Answer 1

You can change the names returned from the query, if you specify each column and an alias. Selecting '*' is anyway not considered best practice as there are some performance hits in the server in comparison with specifying the columns explicitly. Change your query to this:

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

That would be a quick fix. However, your multimapping code doesn't look like standard and I don't see what you need WorkerJobDTO for, it is after all, just a link table. I would change the whole thing to something like this:

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

That's pretty much the standard multi-mapping pattern in Dapper and requires no after-burning.