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

Turbocharge Your .NET Core API with well-known NuGet's

Let’s face it: when your API starts dealing with massive datasets, things can get slow—really slow. Whether you're building an enterprise-level app or a slick startup solution, efficient data retrieval is crucial. In this blog, we’re going to dive deep into the holy trinity of performance optimization in your .NET Core API: Paging, Filtering, and Projection. These techniques will help you speed up data queries, reduce load on your database, and serve your users lightning-fast responses!

We'll cover how to implement these features in .NET Core using popular NuGet packages to keep things simple and efficient. So, let’s get right into it.

📌Explore more at: https://dotnet-fullstack-dev.blogspot.com/

🌟 Restack would be appreciated! 🚀

Why You Need Paging, Filtering, and Projection

Before we jump into the code, let’s break down why these three techniques matter:

• Paging: You don’t want to pull an entire dataset into memory at once—especially not when it could have millions of records. Paging allows you to retrieve data in chunks, making queries faster and responses more manageable.

• Filtering: Not every user cares about all the data. With filtering, you can retrieve only the information that’s relevant to the user’s request. This minimizes both server load and response times.

• Projection: Instead of returning full objects with every single property (which may not even be needed), projection allows you to return only the fields you need. This reduces data transfer overhead and speeds up queries.

Ready to dive into some code? Let’s get to it.

NuGet Packages to Supercharge Your Data Retrieval

Here are some powerful NuGet packages we’ll be using to streamline paging, filtering, and projection:

1. AutoMapper for object mapping and projection.

2. System.Linq.Dynamic.Core for dynamic filtering and sorting.

3. X.PagedList for easy pagination.

Now, let’s go step by step and bring these optimizations to life!

Setting Up the .NET Core API with Paging, Filtering, and Projection

For this example, let’s assume we’re building an Item API where users can retrieve data about various items, but with the ability to filter the data, paginate results, and project only the necessary fields.

Step 1: Install the Required NuGet Packages

First, let’s install the required NuGet packages:

dotnet add package AutoMapper
dotnet add package System.Linq.Dynamic.Core
dotnet add package X.PagedList

Step 2: Create Your Model and DTOs

Let’s start by creating the Item model and a DTO (Data Transfer Object) to use for projection.

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }
    public DateTime CreatedDate { get; set; }
}

// DTO for projection
public class ItemDto
{
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }
}

Step 3: Configure AutoMapper for Projection

Using AutoMapper, you can easily project your entity to the DTO with only the required fields.

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        CreateMap<Item, ItemDto>();
    }
}

Now, you’ll need to configure AutoMapper in your Startup.cs or Program.cs:

services.AddAutoMapper(typeof(MappingProfile));

Step 4: Implement Paging, Filtering, and Projection in the Repository

Here’s the fun part! Let’s implement these features in the repository:

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.

Step 5: Expose the API Endpoint

Now, let’s expose the data retrieval through an API controller:

[ApiController]
[Route("api/[controller]")]
public class ItemsController : ControllerBase
{
    private readonly ItemRepository _itemRepository;

    public ItemsController(ItemRepository itemRepository)
    {
        _itemRepository = itemRepository;
    }

    [HttpGet]
    public async Task<IActionResult> GetItems([FromQuery] string filter, [FromQuery] int pageNumber = 1, [FromQuery] int pageSize = 10)
    {
        var items = await _itemRepository.GetItemsAsync(filter, pageNumber, pageSize);
        return Ok(items);
    }
}

Step 6: Test the API

Now that everything is set up, let’s test the API:

• Fetch items with pagination: /api/items?pageNumber=1&pageSize=5

• Apply dynamic filtering: /api/items?filter=Category == "Books"

In these requests, you'll receive paginated, filtered, and projected data—no more over-fetching, and your API will be as fast as ever.

Wrapping It Up: Why This Matters

By applying paging, filtering, and projection to your API, you're not just making things faster—you’re building a scalable and maintainable solution. You’ll be able to handle large datasets with ease, and your users will thank you for the speedy experience.

Here’s the key takeaway: combining these techniques keeps your application lean, fast, and user-friendly—especially as your data grows. And as you’ve seen, with the help of the right NuGet packages, implementing these optimizations in .NET Core is a breeze.

What’s Next? Try adding sorting and search functionality for even more efficient data handling. Stay tuned for our next blog where we’ll cover those topics and more!