Performance issue with Entity Framework query - even with filters, the request takes too long to complete

Question

[HttpGet("{id}/client-history-sw01-module")]
[Authorize(AuthenticationSchemes = "AdminSchema")]
public async Task<ActionResult<object>> GetClientHistoriesSw01Module(
    long id,
    [FromServices] IMapper mapper,
    string? name,
    DateTime? startDate,
    DateTime? endDate,
    int page = 1,
    int pageSize = 10)
{
    var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

    if (sw01Module == null)
    {
        return StatusCode(404, new { message = "Equipamento não encontrado" });
    }

    if (sw01Module.ClientHistoriesSw01Module == null || !sw01Module.ClientHistoriesSw01Module.Any())
    {
        return StatusCode(404, new { message = "Sem histórico" });
    }

    var clientHistoriesSw01Module = sw01Module.ClientHistoriesSw01Module.AsQueryable();

    if (!string.IsNullOrEmpty(name))
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.ClientName != null && um.ClientName.Contains(name, StringComparison.OrdinalIgnoreCase));
    }

    if (startDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt >= startDate.Value);
    }
    
    if (endDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt <= endDate.Value);
    }

    clientHistoriesSw01Module = clientHistoriesSw01Module
        .OrderByDescending(um => um.CreatedAt);

    var totalItems = clientHistoriesSw01Module.Count();

    var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);

    var paginatedClientHistoriesSw01Module = clientHistoriesSw01Module
        .Skip((page - 1) * pageSize)
        .Take(pageSize);

    var clientHistoriesSw01ModuleGetDTO = mapper.Map<IEnumerable<ClientHistorySw01ModuleGetDTO>>(paginatedClientHistoriesSw01Module);

    var result = new
    {
        Page = page,
        PageSize = pageSize,
        TotalPages = totalPages,
        TotalItems = totalItems,
        Items = clientHistoriesSw01ModuleGetDTO.ToList()
    };

    return result;
}

I'm facing a performance issue with an API endpoint where a query to retrieve data from a database is taking too long to complete. Despite applying filters and pagination, the request still takes a significant amount of time to return results.

The API endpoint retrieves client history data from a Sw01Module entity. It applies various filters (name, start date, end date) and implements pagination, but the request execution is slow.

Answer 1

var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

This eagerly fetches the entirety of the navigation property clienthistoriesSw01Module, so all your filters and pagination after this is done in c# not sql. Move your filters into sql by using a filtered include or select operator.