Do I have to loop through an IEnumerable return from Dapper even though I only return a single object?

Question

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

why you can't just return employee? – Nonik Jan 05 '21 at 23:07 — Nonik, Jan 05 '21 at 23:07

Palle Due · Accepted Answer · 2021-01-06T11:23:59.210

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.

score -1 · Answer 2 · answered Jan 06 '21 at 01:12

-1

My suggestion is that you replace the call connection.QueryAsync<EmployeeModel> by other that returns only one element, as stated here

answered Jan 06 '21 at 01:12

nunohpinheiro

2,169
13
14

1

Thank you for replying. The problem is that my query needs to return multiple rows, so FIRST or SINGLE will not get all the information I'm trying to pull. Several rows must be pulled, then grouped together, which is a function built into Dapper. The code I posted does this well with one level of grouping. I'm looking for syntax to push this to further levels of grouping. – Joshua White Jan 06 '21 at 01:42

Do I have to loop through an IEnumerable return from Dapper even though I only return a single object?

2 Answers2