Data Retrieval with Paging, Filtering, and Projection in .NET Core

We’re going to dive deep into the holy trinity of performance optimization in your .NET Core API: Paging, Filtering, and Projection.

public class ItemRepository
{
    private readonly MyDbContext _context;
    private readonly IMapper _mapper;

    public ItemRepository(MyDbContext context, IMapper mapper)
    {
        _context = context;
        _mapper = mapper;
    }

    public async Task<IPagedList<ItemDto>> GetItemsAsync(
        string filter, 
        int pageNumber = 1, 
        int pageSize = 10)
    {
        // Filtering using Dynamic LINQ
        var query = _context.Items.AsQueryable();

        if (!string.IsNullOrEmpty(filter))
        {
            query = query.Where(filter); // Dynamic filter expression
        }

        // Projection using AutoMapper
        var projectedQuery = query.ProjectTo<ItemDto>(_mapper.ConfigurationProvider);

        // Paging using X.PagedList
        return await projectedQuery.ToPagedListAsync(pageNumber, pageSize);
    }
}

Let’s break this down:

• Filtering: We’re using System.Linq.Dynamic.Core to dynamically filter data based on a string expression (e.g., Category == "Electronics").
• Projection: Using AutoMapper’s ProjectTo() method, we only fetch the fields needed for the ItemDto.
• Paging: The X.PagedList package provides a simple ToPagedListAsync() method to paginate the results.

Explore more at Paging, Filtering, and Projection