In this article, you will learn how to implement pagination over REST API, to manage voluminous data with considerable ease, using pagination technique.
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. |
Columns[]
| Its a json array list of columns for specific search, it contain array of Column objects. |
Column | This json object has two properties, ColumnName and ColumnSearchText , these search provide specific column search feature, e.g.:
Example 1
{
"ColumnName":"Balance",
"ColumnSearchText":"<=,50"
}
It will return all customers with balance less than and equal to 50 .
Example 2
{
"ColumnName":"Country",
"ColumnSearchText":"Pakistan"
}
It will return all customers from country Pakistan only:
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 customer with balance less than equal to 50 and customer's country Pakistan only.
|
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()
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();
ColumnParameter<common.enum.customerpaginationgridcolumns> column =
new ColumnParameter<customerpaginationgridcolumns>() { };
foreach (CustomerPaginationGridColumns columnParse in Enum.GetValues
(typeof(CustomerPaginationGridColumns)))
{
if (!string.IsNullOrEmpty(genericSearchText))
{
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();
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;
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