High-Performance Pagination REST API Query Builder
3.50/5 (3 votes)
Pagination implemented over REST API
Introduction
Indulge a requirement of records pagination over REST API on massive data sources. So we came up with this solution using REST API, hope it will help in your endeavors as well. This article is an extension of my previous datatable.net pagination article, so try not to put plagiarism charges on me. The intention was to perform specific and multiple column searches, sorting, dynamics page sizing, have elegant code structure, least coding effort, and most of all, performance.
Pre-requisites
In order to follow this article, you need to have some understanding of MVC Framework. If you think you have sufficient expertise, then you are ready to further read through this article with ease.
Abstraction
The solution uses Swagger for documentation, and LINQKit package. LINQKit is used to build up flexible and high-performance queries.
Client Request
We are using Postman to query our REST API, the json request structure is as under:
{
"CurrentPage": 1,
"PageSize": 1,
"SearchText": "",
"SortColumn": "Id",
"SortDirection": "Desc",
"HasPagination": true,
"Columns": [
]
}
| Property | Description |
CurrentPage | Requested page number shall be defined in this property, e.g., to have array reponse of 3rd page, then 3 should be assigned to it. |
PageSize | It defines the size of the return arraylist size or page size of request. |
SearchText | To search all the text columns, this property should be populated, e.g., abbot it will search all text column in this case, ContactPerson, Phone, City, Region and Country |
SortColumn | With which column we need to have our data sorted out, e.g., for sorting by contact person, value passed shall be ContactPerson |
SortDirection | For sort by ascending order, Asc should be passed and for descending order, Desc |
HasPagination | To return each and every data produced by this query return as json, it needed to be false, otherwise true should be passed. |
|
| Its a json array list of columns for specific search, it contain array of Column objects. |
Column | This json object has two properties, Example 1
{
"ColumnName":"Balance",
"ColumnSearchText":"<=,50"
}
It will return all customers with balance less than and equal to Example 2
{
"ColumnName":"Country",
"ColumnSearchText":"Pakistan"
}
It will return all customers from country Example 3 You can have multiple scenario search as well, e.g.:
"Columns":[
{
"ColumnName":"Country",
"ColumnSearchText":"Pakistan"
}
,
{
"ColumnName":"Balance",
"ColumnSearchText":"<=,50"
}
]
The above json query shall return |
Consume REST API through Postman Client
To consume this REST API, we are using postman. You can download it from their site. After opening the client, set request type to Post, in body tab, select raw as selected below and set request content type as JSON located far right in combobox.
The above API requests return customers with balance greater than 5000000 with pagination for 1st page.
The snapshot shows all customers from country oman.
The above snapshot demonstrates records in descending order by customer Id number.
REST API Server Side
On server side, we have LINQKit to make our aspiration possible. One of its controller action methods set PaginationRequest object which would be passed by client as define json object in our above examples.
[HttpPost]
[SwaggerResponseExample(HttpStatusCode.OK, typeof(CustomerPaginationResponseExample))]
[SwaggerRequestExample(typeof(PaginationRequest<customerpaginationgridcolumns>),
typeof(CustomerPaginationRequestExample), jsonConverter: typeof(StringEnumConverter))]
[ResponseType(typeof(PaginationResponse<customerpaginationmodel>))]
[HelperMethods.DeflateCompression]
[ValidateModelState]
[CheckModelForNull]
[SwaggerConsumes("application/json")]
[SwaggerProduces("application/json")]
[SwaggerResponse(HttpStatusCode.NotFound,
"No customer found", typeof(GenericResponseModel))]
[Route("")]
public async Task<system.web.http.ihttpactionresult>
Get(PaginationRequest<customerpaginationgridcolumns> request)
{
BusinessLayer.Entity.Customer obj = new BusinessLayer.Entity.Customer(this);
PaginationResponse<customerpaginationmodel> response = obj.Get(request).Result;
if (response.Items == null)
{
return APIResponse(HttpStatusCode.InternalServerError,
$"Error: {obj.errorMessage}");
}
else
if (response.Items.Count() == 0)
{
return APIResponse(HttpStatusCode.NotFound, $"No customer found");
}
return Ok(response);
}
on delving into detail business layer method of our request structured is as under. It dynamically makes up the query using LINQKit and Entity Framework as our ORM. It parses down all columns to defined associated with this business entity and dynamically assembly up the query and implements the pagination simultaneously.
public async Task<paginationresponse<customerpaginationmodel>>
Get(PaginationRequest<common.enum.customerpaginationgridcolumns> paginationRequest)
{
try
{
BusinessEntity.CustomerDBEntities obj =
new BusinessEntity.CustomerDBEntities();
records = (from cus in obj.customers.AsNoTracking()
// join count in obj.countries on
// cus.countryId equals count.countryId
select new CustomerPaginationModel
{
Id = cus.customerID,
ContactPerson = cus.contactPerson,
Phone = cus.phone,
Fax = cus.phone,
City = cus.city,
Region = cus.region,
Country = cus.countryName,
CountryId = cus.countryId,
Balance = cus.balance
}).AsQueryable();
if (paginationRequest.SortColumn != CustomerPaginationGridColumns.None)
{
InitSorting(paginationRequest);
}
else
{
paginationRequest.SortColumn = CustomerPaginationGridColumns.Id;
InitSorting(paginationRequest);
}
genericSearchText = paginationRequest.SearchText == null ?
null : paginationRequest.SearchText.Trim(); // set generic
// search value
ColumnParameter<common.enum.customerpaginationgridcolumns> column =
new ColumnParameter<customerpaginationgridcolumns>() { };
// Iterate through filter grid column to construct query predicate
// foreach (ColumnParameter<common.enum.customerpaginationgridcolumns>
// column in paginationRequest.Columns)
foreach (CustomerPaginationGridColumns columnParse in Enum.GetValues
(typeof(CustomerPaginationGridColumns)))
{
if (!string.IsNullOrEmpty(genericSearchText))
{
// these is no specific column search
if (paginationRequest.Columns.Where
(x => x.ColumnName == columnParse).Count() == 0)
{
column = new ColumnParameter<customerpaginationgridcolumns>()
{ ColumnName = columnParse, ColumnSearchText = "" };
}
else
{
column = paginationRequest.Columns.Where
(x => x.ColumnName == columnParse).FirstOrDefault();
}
}
else
{
column = paginationRequest.Columns.Where
(x => x.ColumnName == columnParse).FirstOrDefault();
}
if (column == null)
{
continue;
}
searchColumnText =
(column.ColumnSearchText ?? "").Trim(); // set current column
// search value
switch (column.ColumnName)
{
case Common.Enum.CustomerPaginationGridColumns.Balance:
EvaluateNumericComparisonFilter(paginationRequest, column,
searchColumnText,
"Balance",
x => x.Balance
);
break;
case Common.Enum.CustomerPaginationGridColumns.City:
EvaluateFilter(paginationRequest, column,
x => x.City.StartsWith(searchColumnText),
x => x.City.StartsWith(genericSearchText),
x => x.City
);
break;
case Common.Enum.CustomerPaginationGridColumns.ContactPerson:
EvaluateFilter(paginationRequest, column,
x => x.ContactPerson.StartsWith(searchColumnText),
x => x.ContactPerson.StartsWith(genericSearchText),
x => x.ContactPerson
);
break;
case Common.Enum.CustomerPaginationGridColumns.Country:
EvaluateFilter(paginationRequest, column,
x => x.Country.StartsWith(searchColumnText),
x => x.Country.StartsWith(genericSearchText),
x => x.Country
);
break;
case Common.Enum.CustomerPaginationGridColumns.CountryId:
if (!IsNumber(searchColumnText))
{
continue;
}
string type = searchColumnText;
EvaluateFilter(paginationRequest, column,
x => x.CountryId == type,
null,
x => x.CountryId
);
break;
case Common.Enum.CustomerPaginationGridColumns.Fax:
EvaluateFilter(paginationRequest, column,
x => x.Fax.StartsWith(searchColumnText),
x => x.Fax.StartsWith(genericSearchText),
x => x.Fax
);
break;
case Common.Enum.CustomerPaginationGridColumns.Phone:
EvaluateFilter(paginationRequest, column,
x => x.Phone.StartsWith(searchColumnText),
x => x.Phone.StartsWith(genericSearchText),
x => x.Phone
);
break;
case Common.Enum.CustomerPaginationGridColumns.Region:
EvaluateFilter(paginationRequest, column,
x => x.Region.StartsWith(searchColumnText),
x => x.Region.StartsWith(genericSearchText),
x => x.Region
);
break;
}
}
PaginationResponse<customerpaginationmodel> response =
new PaginationResponse<customerpaginationmodel>();
IQueryable<customerpaginationmodel> countQuery = records;
response.Items = ForgeGridData(paginationRequest, x => x.ContactPerson).Result;
response.RecordsTotal = totalRows;
// Generating data
return response;
}
catch (Exception exp)
{
CompileExceptionHandleMessage(exp);
return new PaginationResponse<customerpaginationmodel>() { Items = null };
}
finally
{
records = null;
}
}
In Case of Solution Error
In case solution source code refuses to compile, then rebuild the solution, after that, if you have a runtime error, run the following command in your main project's Package Manage Console, then you are good to go.
Update-Package Microsoft.CodeDom.Providers.DotNetCompilerPlatform -r
History
- 16th March, 2021: Initial version