I'm using Dapper to retrieve employee information when I select that employee from a list. Everything maps correctly, and then the rows are grouped according to employee.id. Just what I want. But Dapper returns an IEnumerable, which makes sense when I query for multiple employees and have to make multiple objects; but it makes less sense when I'm only returning the one. Is there a solution to this, or do I just need to loop through the single item? Here's my code:
public async Task<List<EmployeeModel>> GetSelectedEmployee(int selectedEmployeeID)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("WorkDeskDB")))
{
var par = new
{
SelectedEmployeeID = selectedEmployeeID
};
var sql = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname,
em.EmployeeID, em.Address, em.Type,
e.JobTitleID, jt.id, jt.Name,
p.EmployeeID, p.Number, p.Type,
ect.EmployeeID, ect.NameID, ect.InitialDate, ect.ExpirationDate,
ct.id, ct.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.EmployeeCertificationType ect ON ect.EmployeeID = e.id
LEFT JOIN dbo.CertificationType ct ON ect.NameID = ct.id
WHERE e.id = @SelectedEmployeeID";
var employees = await connection.QueryAsync<EmployeeModel, EmailModel, TitleModel, PhoneModel, CertificationModel, EmployeeModel>(sql, (e, em, t, p, c) =>
{
e.EmailList.Add(em);
e.JobTitle = t;
e.PhoneList.Add(p);
e.CertificationList.Add(c);
return e;
},
par, splitOn: "EmployeeID, JobTitleID, EmployeeID, EmployeeID");
var result = employees.GroupBy(e => e.ID).Select(g =>
{
var groupedEmployee = g.First();
groupedEmployee.EmailList = g.Select(e => e.EmailList.Single()).ToList();
return groupedEmployee;
});
return result.ToList();
}
}
You only have one Employee. The other rows are because an Employee can have multiple Phones, Emails and Certifications. I suggest you do something like this:
EmployeeModel employee = null;
await connection.QueryAsync<EmployeeModel, EmailModel, TitleModel, PhoneModel, CertificationModel, EmployeeModel>(sql, (e, em, t, p, c) =>
{
if (employee is null)
{
employee = e;
employee.JobTitle = t;
}
employee.EmailList.Add(em);
employee.PhoneList.Add(p);
employee.CertificationList.Add(c);
return employee;
},
par, splitOn: "EmployeeID, JobTitleID, EmployeeID, EmployeeID");
// go on using employee, no need for the employees list ...
Then you skip the afterburning of the list.