Aggregating Dapper Multi Mapping Results To Object with List Within a List

Question

I'm relatively new to dapper and trying to aggregate the data it returns. I'm trying to figure out how to map results to an object with a list property that also has a list property.

My objects:

public class Employee()
{
    public string Id { get; set; }
    public string Name { get; set; } 
    public List<Employement> Employments { get; set; }
}

public class Employment()
{
    public string CompanyId { get; set; }
    public string Company { get; set; }
    public DateTime HireDate { get; set; }
    public List<JobInfo> JobInfo { get; set; }
}

public class JobInfo()
{
    public string JobTitle { get; set; }
    public string Salary { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime? EndDate { get; set; }
}

My method:

var historyDictionary = new Dictionary<string, Employee>();

var results = await connection.QueryAsync<Employee, Employment, JobInfo, Employee>(
    sqlQuery,
    (e, em, ji) => {
        Employee emp;
        if (!lookup.TryGetValue(e.Id, out emp))
                historyDictionary .Add(e.Id, emp = e);

        if (emp.Employments == null)
                emp.Employments = new List<Employment> { em };
        else if (!emp.Employments.Exists(ec => ec.CompanyId == em.CompanyId))
                emp.Employments.Add(em);

        //map JobInfo

        return emp;          
    }, SplitOn: "Id"
);

I can't seem to wrap my head around mapping the JobInfo. I know I can't just do the same thing I'm doing when mapping Employment since it's a list under Employee and doing Employee.Employments doesn't give me direct access to JobInfo object, so I would assume I would need to use linq

This is what I'm trying to get

Id,
Name,
new List<Employment> {
    new Employment1 {
        CompanyId,
        CompanyName,
        HireDate,
        new List<JobInfo> {
            new JobInfo {
                JobTitle1,
                Salary,
                StartDate,
                EndDate
            },
            new JobInfo {
                JobTitle2,
                Salary,
                StartDate,
                EndDate
            }
        }
    }
    new Employment2 {
        CompanyId,
        CompanyName,
        HireDate,
        new List<JobInfo> {
            new JobInfo {
                JobTitle1,
                Salary,
                StartDate,
                EndDate
            }
        }
    }
}

And would like to make sure the right jobinfo gets mapped to the right employment

Also, I would like to fully understand dapper mapping, is it mapping sql data per row of data?

Sample sql data

Id      Name        CompanyId   Company     HireDate    JobTitle    Salary      StartDate       EndDate
001     employee1   123         company1    01-01-2010  job1        12345       01-01-2010      01-01-2012
001     employee1   123         company1    01-01-2010  job2        23456       01-01-2012      02-02-2015      
001     employee1   456         company2    01-01-2016  job3        34567       03-03-2016      null
002     employee2   123         company1    02-02-2015  job4        23789       02-02-2015      null

Answer 1

Firstly, your Dapper query is not quite right: when using multi-mapping, the objects are split vertically in the resultset (some columns for the parent object, some for the child), and you need to provide the split points i.e. the starting columns for each object.

Then you map each object into its nested location, depending on whether it exists or not. For another level of nesting, you simply take the previous level and lookup again.

var historyDictionary = new Dictionary<string, Employee>();

var results = await connection.QueryAsync<Employee, Employment, JobInfo, Employee>(
    sqlQuery,
    (e, em, ji) => {
        if (!lookup.TryGetValue(e.Id, out var emp))
            historyDictionary.Add(e.Id, emp = e);

        if (emp.Employments == null)
            emp.Employments = new List<Employment> { em };
        else
        {
            em2 = emp.Employments.Find(ec => ec.CompanyId == em.CompanyId);
            if(em2 == null)
                emp.Employments.Add(em);
            else
                em = em2;
        }

        em.JobInfo = em.JobInfo ?? new List<JobInfo>();
        em.JobInfo.Add(ji);

        return emp;          
    }, SplitOn: nameof(Employment.CompanyId) + "," + nameof(JobInfo.JobTitle)
);

You can simplify the logic a bit by assigning default List objects in the constructors. Normally I would use Dictionary instead of List, and that is also simpler for doing lookups:

var historyDictionary = new Dictionary<string, Employee>();

var results = await connection.QueryAsync<Employee, Employment, JobInfo, Employee>(
    sqlQuery,
    (e, em, ji) => {
        if (!lookup.TryGetValue(e.Id, out var em2))
            historyDictionary.Add(e.Id, em2 = e);

        if (!emp.Employments.TryGetValue(em.CompanyId, out var em2))
            emp.Employments.Add(em.CompanyId, em2 = em);

        em2.JobInfo.Add(ji);

        return emp;          
    }, SplitOn: nameof(Employment.CompanyId) + "," + nameof(JobInfo.JobTitle)
);

You should also look into QueryMultiple, where you have multiple SELECT queries, and you map them in yourself. This prevents a lot of duplicate rows in some circumstances.