CatConsult.PaginationHelper 2.1.0

• Pagination Helper
• Features
• Getting Started
• Quick Example
• Documentations
  • Query Parameters
  • Filters
  • Property Types
  • PaginateOptionsBuilder
  • ToPaginatedAsync
  • IPaginateResult
• Local Development

Pagination Helper

A dotnet entity framework extension class to dynamically perform server-side data processing (paging, sorting, searching, and filtering). The extension method is extended on top of EntityFramework IQueryable type. Try this out if you're tired of duplicated 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 translated into SQL
• Secured. Built with Dynamic LinQ, say no to SQL Injection

Getting Started

1. Install NuGet Package

> dotnet add package CatConsult.PaginationHelper

2. Import Package

using CatConsult.PaginationHelper;

3. Use ToPaginateAsync

DbContext.FooEntities.ToPaginatedAsync(paginateOptionsBuilder)

Quick Example

For more examples, checkout 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 project into a dto first, better for the 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=desk&strCol__eq=hello

^^^ the above example request will return all rows with StrCol contains "hello", order 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 second page, 10 rows per page, rows that contains "something" in StrCol or OtherCol, StrCol contains "filter me", DateCol greater than or equal to 2000-1-1 date,

Documentations

Query Parameters

Filters

We included some built-in filter keywords to provide more flexible filtering. Filter keyword can be append to any filter key and they are case-insensitive. All filter keywords start with two underscore, __XX. For example, if you want any strCol start with "A", instead of strCol=A you will do strCol__start=A.

All filter keys accept multiple values.

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

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

Property Types

Some filtering behavior differs based on the item property type.

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

When perform search, all types will use their default filter type.

PaginateOptionsBuilder

A helper class to build paginate option. The class can be use 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 column from search or filter
builder.ExcludeColumns("strCol", "fooCol")

// to only include defined column 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>. Will return IPaginateResult<T>

// first parameter take PaginateOptionsBuiler
// optional second parameter to transform IQueryable after apply 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 support C#

Running the Test

Use dotnet test command in project directory. The test will be running against real PostgresSQL database using Docker Fixture.