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 3^rd 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 1^st 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

16^th March, 2021: Initial version

High-Performance Pagination REST API Query Builder