How can I sort a list of Employees by the SortOrder of their Roles?

c# entity-framework-core linq


I'm trying to sort a list of employees which can have any number of different roles. The roles themselves are sorted by the SortOrder property, and I want the employees to be sorted according to the top sorted of all the roles that are assigned to them.


SortOrder - Role
1 - "Manager"
2 - "Graphics designer"
3 - "Server-tech-guy"
4 - "Web developer"
5 - "Coffee Machine manager"

An employee could be both graphics designer and manage the coffee machine. In that case, I only want to use the SortOrder of the role "Graphics designer" when sorting the list of employees.

Here are my models:

public class Employee
    public int Id { get; set; }
    public int BranchId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    public double EmploymentPercentage { get; set; }
    public double HourlyWage { get; set; }
    public List<EmployeeRole> EmployeeRoles { get; set; }
    public Branch Branch { get; set; }

public class EmployeeRole
    public int Id { get; set; }
    public int EmployeeId { get; set; }
    public int RoleId { get; set; }
    public Employee Employee { get; set; }
    public Role Role { get; set; }

public class Role
    public int Id { get; set; }
    public string Title { get; set; }
    public int SortOrder { get; set; }

    public Branch Branch { get; set; }

This is the query I have so far:

List<Employee> employees = await db.Employees
    .Include(er => er.EmployeeRoles)
        .ThenInclude(r => r.Role)
    .Where(b => b.Branch.Id == BranchId)
    .OrderByDescending(r => r.EmployeeRoles.Min(s => s.Role.SortOrder))
        .ThenByDescending(p => p.EmploymentPercentage)
            .ThenBy(n => n.LastName)

In this query I tried to find the role with the lowest SortOrder-number for each employee (.Min(s => s.Role.SortOrder), but it's not doing what I expected. I'm getting

InvalidOperationException: Sequence contains no elements.

1/24/2019 2:30:51 PM

Accepted Answer

Non nullable overloads of Min and Max methods throw exception when the source sequence is empty (for instance if you have some Employee without assigned Roles).

However the nullable overloads do not throw exception, but simply return null. Hence the solution is to promote the non nullable type to the corresponding nullable type. Additionally ?? operator can be used to designate a special value for that case.

In your case it can be something like this:

.OrderByDescending(r => r.EmployeeRoles.Min(s => (int?)s.Role.SortOrder) ?? 0)
1/24/2019 2:58:32 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow