How do I find the top 5 rows of a LINQ query result?

I have an ASP.NET Core MVC app that contains two entities: Employee and Task. These entities have a one-to-many relationship.

A Task consists of an assignee (the Employee assigned to work on it) and a due date, among other properties.

I need to display the 5 employees who completed the largest number of tasks in the past month.

I tried the following:

public async Task<IActionResult> Display()
{
    int current_month = DateTime.Now.Month;

    var employees = _context.Employees
        .Include(t => t.EmployeeTasks)
        .OrderByDescending(x => 
            x.EmployeeTasks.Count(x => x.DueDate.Month == current_month - 1))
        .Take(5);

    return View(employees);
}

This query is not working properly, as if it does not recognize the condition

This query isn't working properly - it seems like it doesn't recognize the condition
<br>
DueDate.Month == current_month - 1.

Does anyone know how to correct this query?

Using the current_month variable to filter tasks by due date might be causing the query issue. If the current month is January (which has a value of 1), subtracting 1 from current_month might not always result in the expected result.

To resolve this issue, you can modify the query to filter tasks based on their due dates using the DateTime properties Month and Year. Using the following query, you can filter tasks due in the previous month

// Get the start and end dates of the previous month
    var currentDate = DateTime.Now;
    var startDate = new DateTime(currentDate.Year, currentDate.Month, 1).AddMonths(-1);
    var endDate = startDate.AddMonths(1).AddDays(-1);

    // Retrieve the top 5 employees who completed the most tasks in the previous month
    var employees = await _context.Employees
        .Include(e => e.EmployeeTasks)
        .OrderByDescending(e => e.EmployeeTasks.Count(t => t.DueDate >= startDate && t.DueDate <= endDate))
        .Take(5)
        .ToListAsync();