Skip to content

A dynamic server-side-processing pagination helper for .Net Entity Framework

License

Notifications You must be signed in to change notification settings

Catalyst-Consulting-Group/dotnet-pagination-helper

Repository files navigation

Test Coverage NuGet stable version

Pagination Helper

A .NET Entity Framework extension class to dynamically perform server-side data processing (paging, sorting, searching, and filtering). This extension method is built on top of EntityFramework's IQueryable type. Try it out if you're tired of duplicating server-side processing boilerplate code!

Features

  • Dynamically paginate, sort, filter, and search data
  • Easy to use on top of existing EF code
  • Flexible: Lots of built-in options to perform general filters
  • Fast: No in-memory operation, everything is translated into SQL
  • Secure: Built with Dynamic LinQ, protecting against SQL Injection

Getting Started

  1. Install NuGet Package:

    > dotnet add package CatConsult.PaginationHelper
  2. Import Package:

    using CatConsult.PaginationHelper;
  3. Use ToPaginatedAsync:

    DbContext.FooEntities.ToPaginatedAsync(paginateOptionsBuilder)

Quick Example

For more examples, check out the integration tests class.

ASP.Net API Project

...

// Example entity
public class FooEntity
{
    public string StrCol { get; set; }
    public string OtherCol { get; set; }
    public DateTimeOffset DateCol { get; set; }
}

...
using CatConsult.PaginationHelper;
...

// A random controller method
public async Task<IPaginateResult<FooDto>> GetPaginatedData([FromQuery] PaginateOptionsBuilder paginateOptionsBuilder)
{
    return await _db.FooEntities
        .Where(...) // Pre-filter data if needed
        .Select(...) // Recommend projecting into a DTO first, better for performance
        .ToPaginatedAsync(paginateOptionsBuilder);
}
...

Returned Data Type (IPaginateResult)

{
  data: any[]; // List of paginated data
  count: number; // Total matched records in the database
  currentPage: number;
  rowsPerPage: number;
  totalPages: number;
  previousPage: number | null; // Null if no previous page
  nextPage: number | null; // Null if no next page
}

Example HTTP Requests

HTTP GET /paginated?order=dateCol&orderDirection=desc&strCol__eq=hello

The above example request will return all rows where StrCol equals "hello", ordered by DateCol in descending order.

HTTP GET /paginated?page=1&rowsPerPage=10&strCol=filter me&dateCol__gte=2000-1-1

The above example request will return the second page, with 10 rows per page, where rows contain "filter me" in StrCol and DateCol is greater than or equal to 2000-1-1.

Documentation

Query Parameters

Name Description
page Page number starting from 0
rowsPerPage Maximum rows to return per page
orderBy The property name of the item to order/sort by
orderDirection Order/sort direction. Supports asc or desc
search Search value. Returns items with matching columns values
columns Columns to search in. *Required if search is provided
other keys All other keys will be treated as filters. Check out the Filters Section for more details

Filters

Built-in filter keywords provide more flexible filtering. Filter keywords can be appended to any filter key and are case-insensitive. All filter keywords start with __XX. For example, to filter for any strCol starting with "A", use strCol__start=A instead of strCol=A.

Keyword Filter Type Applicable Property Types Description Note
__in Contains String and List type only Item value contains filter value Default filter type for String and List types.
String type contains is case-insensitive.
List type contains is case-sensitive
__eq Equal All except List / Object type Filter value == item value Default filter type for others except String and List types
String type equal is case-insensitive
Date type compares only Date part. For time comparison, use greater/less than filter keywords
__gt Greater Than All, except List / Object type Filter value > item value
__gte Greater Than or Equal to All, except List / Object type Filter value >= item value
__lt Less Than or Equal to All, except List / Object type Filter value < item value
__lte Less Than or Equal to All, except List / Object type Filter value <= item value
__start Starts With String or List of string type only Item value starts with filter value
__end Ends With String or List of string type only Item value ends with filter value

All filter keys accept multiple values.

  • Range keywords (gt, gte, lt, and lte) join using && operation.
  • All other keywords join using || operation.

For example, str=A&str=B&num_gte=1&num_lt=10 translates into (str == A || str == B) && (num >= 1 && num < 10).

Property Types

Filtering behavior differs based on the item property type.

  • String and List types use Contains filter by default. For example, str=A translates into str contains 'A'.
  • Other types use Equal filter by default. For example, number=1 translates into number equals 1.

When performing a search, all types use their default filter type.

PaginateOptionsBuilder

A helper class to build paginate options. The class can be used to bind query params in the controller.

PaginateOptionsBuilder builder = new PaginateOptionsBuilder();

// To add new keys
builder
    .Add("orderBy", "date")
    .Add("orderDirection", "desc")
    .Add("strCol", "A", "B", "C")

// To exclude some columns from search or filter
builder.ExcludeColumns("strCol", "fooCol")

// To only include defined columns for search or filter
builder.IncludeColumns("strCol", "fooCol")

// Override default filters by type
builder.OverrideDefaultFilterType(FilterPropertyType.String, FilterPropertyType.StartWith);

ToPaginatedAsync

Extension method of IQueryable<T>. Returns IPaginateResult<T>.

// First parameter takes PaginateOptionsBuilder
// Optional second parameter to transform IQueryable after applying filter, paginate, and sort
DbContext.Entities.ToPaginated(paginateOptionsBuilder, paginatedQuery => paginatedQuery.Where(c => c.FooBool))

IPaginateResult

public interface IPaginateResult<T>
{
    IEnumerable<T> Data { get; set; } // Paginated and filtered item list
    int Count { get; set; } // Total matched records in the database
    int CurrentPage { get; set; }
    int RowsPerPage { get; set; }
    int TotalPages { get; }
    int? PreviousPage { get; }
    int? NextPage { get; }
}

Local Development

Prerequisites

  • Windows or macOS
  • .NET >6
  • Docker Desktop
  • Editor/IDE that supports C#

Running the Tests

Use the dotnet test command in the project directory. The tests will run against a real PostgreSQL database using Docker Fixture.