c# - Dapper maps object using second ID column rather than first

Question

My SQL query returns employee information which I map with dapper. The SQL returns the PK from the Employee table, along with various other columns, then the PK of each child object with their columns. But the first object, an EmployeeModel, receives the PK of the first child object. I've tried reordering the SQL query without success. Perhaps I'm misunderstanding the Dapper syntax?

var sql = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname, 
                                    em.id, em.Address, em.Type, 
                                    jt.id, jt.Name, 
                                    p.id, p.Number, p.Type,
                                    d.id, d.Name,
                                    es.id, es.Name
                                   

                          FROM dbo.Employees e 
                          LEFT JOIN dbo.Emails em
                          ON em.EmployeeID = e.id
                          LEFT JOIN dbo.JobTitles jt                           
                          ON e.JobTitleID = jt.id
                          LEFT JOIN Phones p
                          ON p.EmployeeID = e.id
                          LEFT JOIN dbo.Departments d
                          ON e.DepartmentID = d.id
                          LEFT JOIN dbo.EmployeeStatus es  
                          ON e.StatusID = es.id";

                var employees = await connection.QueryAsync<EmployeeModel,
                                                            EmailModel,
                                                            TitleModel,
                                                            PhoneModel,
                                                            DepartmentModel,
                                                            EmployeeModel>
                                                            (sql, (e, em, t, p, d) =>
                {
                    e.EmailList.Add(em);
                    e.JobTitle = t;
                    e.Department = d;
                    e.PhoneList.Add(p);
                    return e;
                },
                splitOn: "id, id, id, id, id");

Here's the SQL result. The first EmployeeModel, for instance, ends up with ID = 2 instead of 9, and so on. (The user info is fictional.)

9   Abed    Nadir   Abed    2   [email protected]    Campus  9   Human Resources Manager 4   555-212-1345    Home    2   Sales   1   Active
9   Abed    Nadir   Abed    2   [email protected]    Campus  9   Human Resources Manager 13  555-224-7894    Work    2   Sales   1   Active
9   Abed    Nadir   Abed    6   [email protected]    Personal    9   Human Resources Manager 4   555-212-1345    Home    2   Sales   1   Active
9   Abed    Nadir   Abed    6   [email protected]    Personal    9   Human Resources Manager 13  555-224-7894    Work    2   Sales   1   Active
9   Abed    Nadir   Abed    7   [email protected]    Personal    9   Human Resources Manager 4   555-212-1345    Home    2   Sales   1   Active
9   Abed    Nadir   Abed    7   [email protected]    Personal    9   Human Resources Manager 13  555-224-7894    Work    2   Sales   1   Active
10  Jeffrey Winger  Winger  1   [email protected]   Campus  3   Machinist   5   555-212-4567    Home    3   Manufacturing   4   Suspended
10  Jeffrey Winger  Winger  1   [email protected]   Campus  3   Machinist   6   555-207-5432    Work    3   Manufacturing   4   Suspended
10  Jeffrey Winger  Winger  1   [email protected]   Campus  3   Machinist   7   555-207-2145    Cell    3   Manufacturing   4   Suspended
10  Jeffrey Winger  Winger  3   [email protected]  Work    3   Machinist   5   555-212-4567    Home    3   Manufacturing   4   Suspended
10  Jeffrey Winger  Winger  3   [email protected]  Work    3   Machinist   6   555-207-5432    Work    3   Manufacturing   4   Suspended
10  Jeffrey Winger  Winger  3   [email protected]  Work    3   Machinist   7   555-207-2145    Cell    3   Manufacturing   4   Suspended
11  Annie   Edison  Annie   4   [email protected]  Campus  3   Machinist   8   555-225-3754    Cell    5   Receiving   1   Active
13  Pierce  Hawthorn    Pierce  5   [email protected]   Campus  9   Human Resources Manager 10  555-225-3525    Home    6   Finance 5   Sabbatical
14  Shirley Bennett Shirley 1006    [email protected]   Campus  8   Accounts Payable Clerk  14  555-555-6792    Home    8   Equipment   4   Suspended
14  Shirley Bennett Shirley 1007    [email protected]  Personal    8   Accounts Payable Clerk  14  555-555-6792    Home    8   Equipment   4   Suspended
15  Troy    Barnes  Troy    1008    [email protected]   Campus  9   Human Resources Manager 15  555-229-2855    Cell    2   Sales   2   Former
15  Troy    Barnes  Troy    1009    [email protected] Personal    9   Human Resources Manager 15  555-229-2855    Cell    2   Sales   2   Former

question from:https://stackoverflow.com/questions/65842773/dapper-maps-object-using-second-id-column-rather-than-first

Answer 1

Try something like this:

var sql = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname, 
                                em.id as em_id, em.Address as em_Address, em.Type as em_Type, 
                                jt.id as jt_id, jt.Name as jt_Name, 
                                p.id as p_id, p.Number as p_Number, p.Type as p_Type,
                                d.id as d_id, d.Name as d_Name,
                                es.id as es_id, es.Name as es_Name
                               

                      FROM dbo.Employees e 
                      LEFT JOIN dbo.Emails em
                      ON em.EmployeeID = e.id
                      LEFT JOIN dbo.JobTitles jt                           
                      ON e.JobTitleID = jt.id
                      LEFT JOIN Phones p
                      ON p.EmployeeID = e.id
                      LEFT JOIN dbo.Departments d
                      ON e.DepartmentID = d.id
                      LEFT JOIN dbo.EmployeeStatus es  
                      ON e.StatusID = es.id";

            var employees = await connection.QueryAsync<EmployeeModel,
                                                        EmailModel,
                                                        TitleModel,
                                                        PhoneModel,
                                                        DepartmentModel,
                                                        EmployeeModel>
                                                        (sql, (e, em, t, p, d) =>
            {
                e.EmailList.Add(em);
                e.JobTitle = t;
                e.PhoneList.Add(p);
                e.Department = d;
                return e;
            },
            splitOn: "em_id, jt_id, p_id, d_id");