I’m using Dapper to return employee information from my Database and map the columns to the properties of EmployeeModel. It’s also grouping the rows by employee id, so there aren’t multiple objects made for each Email address. It was working great, but I’ve added new selects to the query, such as Boolean values for whether certain trainings have been completed. So now I need a second, third, fourth level of grouping, etc. Could someone help me with that syntax?
I think you’re over complicating things here. First your SQL should return your model. You shouldn’t have to do extra steps to “make it fit” your model. Highly recommend putting your SQL into a stored procedure. It’s much easier to maintain that way.
Perhaps you’re right, and if so, I’m sooo willing to learn. But as I understand it, we use ORMs such as Dapper (or Entity Framework) to map the SQL to our models. So for instance, the code I posted pulls information from multiple tables and maps it to Employee.FirstName, Employee.LastName, Employee.EmailList, etc., then groups all the rows where Employee.id is the same.
This way I have a completely populated EmployeeModel from one SQL query with several joins. I can then bind any number of controls to the various EmployeeModel properties. If there is another way to do this, I’ll be glad to learn it.
This isn’t a c# thing, this is more of a r/SQL issue
The syntax I’m looking for is in Dapper, which is a c# reference library.
I don’t know what your database design looks like but you’re most likely having an issue with the SQL one to many relationship between the employee/email table and the certifications table. If an employee has multiple certifications/emails a record will be returned for each cert/email.
My suggestion would be to have private methods for getting the emails/certifications based on the employee ID then await Task.WhenAll and add the email/certification lists to the employeemodel.
On mobile so not syntactically correct but you get the idea:
public async Task<EmployeeModel> GetSelectedEmployee(int selectedEmployeeID) {
var employee = await GetEmployee(selectedEmployeeID);
var certTask = GetEmployeeCerts(selectedEmployeeID);
var emailsTask = GetEmployeeEmailList(SelectedEmployeeID);
await Task.WhenAll(new List<Task>{certTask,emailsTask};
employee.EmailList = await emailsTask;
employee.CertificationList = await certTask;
return employee;
}
This isn’t correct. You wouldn’t use Task.WhenAll for this. You would simply await GetEmployeeCerts and GetEmployeeEmailList the same as you did for GetEmployee. You’re doing that anyway when you await emailTask and certTask (event though they don’t each spawn threads there, the result is the same).
I think you need to alias each of those spilt on columns so they aren’t all EmployeID
The order of columns in the select list matters. All columns in EmployeeModel must come first, then EmailModel, etc. You have e.JobTitleID listed after the EmailModel columns. Dapper uses the order to split the columns and assign values to the appropriate models.
I think order also matters in the type parameters for QueryAsync. They have to match the order the columns appear in the select clause.