DEV Community

DotNet Full Stack Dev
DotNet Full Stack Dev

Posted on

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

#dotnetcore #csharp #dotnet #paging

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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

Top comments (2)

Collapse
 
renanpaivag profile image
Renan de Paiva Dias • • Edited

Pretty awesome, just 2 cents: why not

using System.Linq.Expressions;
using X.PagedList; // For paging
using AutoMapper;
using AutoMapper.QueryableExtensions;
using Microsoft.EntityFrameworkCore;

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(
        Expression<Func<Item, bool>> filter = null,
        int pageNumber = 1, 
        int pageSize = 10)
    {
        // Start with the base query
        var query = _context.Items.AsQueryable();

        // Apply the filter if provided
        if (filter != null)
        {
            query = query.Where(filter);
        }

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

        // Return paginated result
        return await projectedQuery.ToPagedListAsync(pageNumber, pageSize);
    }
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
dotnetfullstackdev profile image
DotNet Full Stack Dev •

@renanpaivag Thanks for reading and your appreciation! I seen one major change in your code

 // Apply the filter if provided
        if (filter != null)
        {
            query = query.Where(filter);
        }
Enter fullscreen mode Exit fullscreen mode

Where filter is a func delegate, with the purpose of filter return true or false based on Item matches.. it will help when you want to do filtration based on entire Item
Case 1

Item Price and Item Name and Item Type (All should match)

It will check entire Item has be same values.
If suppose my use case is
Case 2

  1. Give me Items, whose Price is Less than 1000, with no filter on Name and Type
  2. Give me Items, whose Names like 'XYZ', no filter on Price and Type
  3. Give me Items, whose Type is 'electronics', with no filter on Name and Type

This Case 2 will work with the source code and also entire Item values (Case 1)

Hope I cleared it, always welcome for your thoughts.

Read next

peranv profile image

🔐 How to Implement OAuth 2.0 Authentication in ASP.NET Core with External APIs

Esperanza Najera -

terieyenike profile image

Building a Console-Based Calculator in C#

oteri -

moh_moh701 profile image

Understanding and Writing Methods in C#

mohamed Tayel -

leandroveiga profile image

Master Advanced LINQ in C#: Writing Ultra-Efficient Queries for Massive Datasets

Leandro Veiga -