Pagination, Filtering & Sorting

কেন Pagination?

10000 record একসাথে return করলে memory + bandwidth খরচ। Page-wise data দ্রুত।

Query Parameter DTO

public class ProductQuery {
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 20;
    public string? Search { get; set; }
    public string? Category { get; set; }
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    public string? SortBy { get; set; } = "Id";
    public bool SortDesc { get; set; } = false;
}

Paged Result Wrapper

public class PagedResult<T> {
    public List<T> Items { get; set; } = new();
    public int TotalCount { get; set; }
    public int Page { get; set; }
    public int PageSize { get; set; }
    public int TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
    public bool HasNext => Page < TotalPages;
    public bool HasPrev => Page > 1;
}

Endpoint Implementation

[HttpGet]
public async Task<ActionResult<PagedResult<ProductDto>>> List([FromQuery] ProductQuery q) {
    IQueryable<Product> query = _ctx.Products.AsNoTracking();

    // Filter
    if (!string.IsNullOrWhiteSpace(q.Search))
        query = query.Where(p => p.Name.Contains(q.Search));
    if (!string.IsNullOrWhiteSpace(q.Category))
        query = query.Where(p => p.Category == q.Category);
    if (q.MinPrice.HasValue)
        query = query.Where(p => p.Price >= q.MinPrice);
    if (q.MaxPrice.HasValue)
        query = query.Where(p => p.Price <= q.MaxPrice);

    // Sort dynamic
    query = q.SortBy?.ToLower() switch {
        "name" => q.SortDesc ? query.OrderByDescending(p => p.Name) : query.OrderBy(p => p.Name),
        "price" => q.SortDesc ? query.OrderByDescending(p => p.Price) : query.OrderBy(p => p.Price),
        _ => q.SortDesc ? query.OrderByDescending(p => p.Id) : query.OrderBy(p => p.Id)
    };

    var total = await query.CountAsync();
    var items = await query
        .Skip((q.Page - 1) * q.PageSize)
        .Take(q.PageSize)
        .Select(p => new ProductDto(p.Id, p.Name, p.Price))
        .ToListAsync();

    return Ok(new PagedResult<ProductDto> {
        Items = items, TotalCount = total,
        Page = q.Page, PageSize = q.PageSize
    });
}

URL Example

GET /api/products?page=2&pageSize=10&search=laptop&minPrice=500&sortBy=price&sortDesc=true

Cursor-based Pagination (Better for Large Data)

// id > lastId offset-এর চেয়ে দ্রুত
var items = await _ctx.Products
    .Where(p => p.Id > cursor)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .ToListAsync();
var nextCursor = items.LastOrDefault()?.Id;

Response Headers (REST)

Response.Headers.Add("X-Total-Count", total.ToString());
Response.Headers.Add("X-Page", q.Page.ToString());
Response.Headers.Add("Link", $"<...?page={q.Page+1}>; rel=next");