Linq get id of the employee with most entries in table

user_15567924 0
Viewed 74

i am trying to write a linq that will return the id of the employee who has the most entries in the table.

This is how my class looks like

public class TrainingEmployee
{
    public int EmployeeId { get; set; }
    public int TrainingId { get; set; }

    public List<TrainingEmployee> GenerateData()
    {
        return new List<TrainingEmployee>()
        {
            new TrainingEmployee() { EmployeeId = 1, TrainingId = 2},
            new TrainingEmployee() { EmployeeId = 1, TrainingId = 2},
            new TrainingEmployee() { EmployeeId = 1, TrainingId = 2},
            new TrainingEmployee() { EmployeeId = 1, TrainingId = 2},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 3},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 3},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 3},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 3},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 5},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 5},
            new TrainingEmployee() { EmployeeId = 2, TrainingId = 1},

        };
    }
}

And this is how my code looks so far

var lista = new TrainingEmployee();

        var data = lista.GenerateData().GroupBy(x => x.EmployeeId);
        var maxValue = 0;
        var employeeId = 0;
        foreach (var group in data)
        {
            
            var currentlyGroupCount = group.Count();
            if(currentlyGroupCount > maxValue)
            {
                maxValue = currentlyGroupCount;
                employeeId = group.Key;
            }
        }
        Console.WriteLine("Value: {0} employeeid: {1}", maxValue, employeeId);

How can i do the above code in just a linq without using that much of a code?

linq .net c#
3 Answers

You could order it descending and select the first one:

var employee = GenerateData()
    // group on EmployeeId
    .GroupBy(e => e.EmployeeId)
    // reverse order it on count
    .OrderByDescending(g => g.Count())
    // select the first
    .FirstOrDefault();

// check if the query returned anything other than default.
if(employee != default)
    Console.WriteLine("Value: {0} employeeid: {1}", employee.Count(), employee.EmployeeId);
user_22661300

Another approach similar to jeroen-van-langen's answer but using MoreLINQ's MaxBy():

GenerateData()
  .GroupBy(e => e.EmployeeId)
  .MaxBy(e => e.Count());

This would also return multiple IDs if multiple employee's had the same "max count"; a possibility in your scenario.

user_20517700

This evaluates Count() once for each employees group so is a little more performant, it allows also to get both of employyId and the max count

    var mostFrequentEmployeeId = GenerateData()
        .GroupBy(x => x.EmployeeId, (employeeId, employeesGroup) => new { employeeId, count = employeesGroup.Count() })
        .OrderByDescending(x => x.count)
        .FirstOrDefault()?
        .employeeId;
user_2695990
Related