Using LINQ to select highest earning employee per department

Question

Not really sure how to word it but basically I have a bunch of data in a list that goes similar to this:

Person 1

Name - Joe Bloggs
Age - 40
Department - IT
Wage - 20,000

Person 2

Name - Jess Jane
Age - 40
Department - Kitchen
Wage - 16,000

...you get the idea.

At the moment, I've just selected all of the people, ordered them by wage and entered them in a listbox very simply by doing this.

var item = (from employee in employeeList.employees
orderby employee.wage descending
select employee);

Now, my question is, how can I change this bit of code so that it filters through the list and show only the highest earning employee in their department? So for example, instead of having hundreds of employees listed, it will only show the highest earning employee in IT, then the highest earning employee in catering, etc.

Is it possible? If not are there any other methods I can use to achieve this?

Answer 1

You could try something like this:

var result =  employeeList.employees.GroupBy(emp => emp.Departemnt)
                                    .Select(gr => new 
                                    { 
                                        Departemnt = gr.Key, 
                                        Employee = gr.OrderByDescending(x=>x.wage)
                                                     .FirstOrDefault() 
                                    });

That we do above is a grouping by department and the we pick for each department the employee with the highest wage.

Update

In .NET 6, the above could be re-written as below:

var result =  employeeList.employees
                          .GroupBy(employee => employee.Departemnt)
                          .Select(gr => new 
                          { 
                              Departemnt = gr.Key, 
                              Employee = gr.MaxBy(x => x.wage) 
                          });

Answer 2

This approach lets you get the result without ordering. So it will take only O(n) time instead of O(n*log(n))

var highestEarningPersonByDepartment = persons
    .GroupBy(p => p.Department)
    .Select(g => new { Department = g.Key, HighestEarningPerson = g.First(person => person.Wage == g.Max(p => p.Wage)) })
    .ToDictionary(dp => dp.Department, dp => dp.HighestEarningPerson);

Edit: A more optimised version would be:

var maxWageByDepartment = persons
    .GroupBy(p => p.Department)
    .ToDictionary(g => g.Key, g => g.Max(p => p.Wage));

var richestPersonByDepartment = persons
    .GroupBy(p => p.Department)
    .Select(g => new { Department = g.Key, HighestEarningPerson = g.First(person => person.Wage == maxWageByDepartment[g.Key]) });