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!
- 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
- Install NuGet Package
> dotnet add package CatConsult.PaginationHelper
- Import Package
using CatConsult.PaginationHelper;
- Use ToPaginateAsync
DbContext.FooEntities.ToPaginatedAsync(paginateOptionsBuilder)
For more examples, checkout integration tests class
...
// 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);
}
...
{
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
}
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,
Name | Description |
---|---|
page | Page number start with 0 |
rowsPerPage | Maximum rows to return for a page |
orderBy | The property name of the item to order/sort by |
orderDirection | order/sort by direction. Support asc or desc |
search | Search value. Return items with matching columns values |
columns | Columns to search for. *Required if provide search |
other keys | All other keys will be treated as filter . Checkout Filters Section for more detail |
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
.
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 type. String type contains is case-insensitive.String and List type only 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 typeString type equal is case-insensitiveDate type only compare Date part. If need to narrow down by time, use greater/less than filter keyword |
__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 type only |
item value starts with filter value |
|
__end | Ends With | String type only |
item value ends with filter value |
All filter keys accept multiple values.
- Range keywords (
gt
,gte
,lt
, andlte
) 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)
Some filtering behavior differs based on the item property type.
String
andList
type will useContains
filter by default. For example,str=A
will translate intostr contains 'A'
- Other types will use
Equal
filter by default. For example,number=1
will translate intonumber equal 1
When perform search
, all types will use their default filter type.
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);
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))
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; }
}
- Windows or macOS
- .NET 6
- Docker Desktop
- Editor/IDE that support C#
Use dotnet test
command in project directory. The test will be running against real PostgresSQL database using Docker Fixture.