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.