Dapper使用第二个ID列而不是第一列映射对象

Question

我的SQL查询返回我用dapper映射的员工信息。SQL返回Employee表中的PK和其他列，然后返回每个子对象及其列的PK。但是第一个对象是EmployeeModel，它接收第一个子对象的PK。我试过重新排序SQL查询没有成功。也许我误解了Dapper的语法?

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

下面是SQL结果。例如，第一个EmployeeModel以ID = 2而不是9结束，以此类推。(用户信息是虚构的)

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

Answer 1

试试这样:

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