ASP.NET Web API Pagination using OData operators

ASP.NET Web API is a framework that makes it easy to build HTTP services for various types of clients from Microsoft to Non-Microsoft technologies. The new programming model of developing over HTTP is made simple and flexible by using WebAPI. We can design services which can be accessible from a broad range of clients, including browsers and mobile devices.

One of the most frequent requirements while retrieving data using WEB API is that how to implement pagination so that only a subset of data is fetched from the server. To implement this, OData queries with WEB API can be used. You can get more information about the OData Queries from here.

Let us build a simple application that can filter multipage data using OData queries directly.

The Web API Demo

Step 1: Open VS 2013 and create a new Empty MVC application. Name it as ‘MVC50_WEBAPI_Pagination’. Since we will be using jQuery and Knockout.js framework, in this project right click and using ‘Manage NuGet Package’, get the latest jQuery and Knockout script files.

Step 2: Add a new SQL Server Database in the project as below:

Name it as Application.MDF. This database will be added in the App_Data folder. Double click on the MDF file, the database will be shown in the ‘Server Explorer’. Add the new table in the database of name ‘EmployeeInfo’ as below:

The SQL Script is as below:

CREATE TABLE [dbo].[EmployeeInfo] (
    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,
    [EmpName]     VARCHAR (50) NOT NULL,
    [DeptName]    VARCHAR (50) NOT NULL,
    [Salary]      INT          NOT NULL,
    [Designation] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([EmpNo] ASC)
);

Add about 20 rows of sample data.

Step 3: In the Models folder, add a new ADO.NET EF model, Complete wizard, provide the Database name as the Application.MDF added in the application in Step 2. After completing the Wizard the mapping will be shown as below:

Step 4: In the controller folder, add a new API controller based upon the ADO.NET EF added in previous step. Name it as ‘EmployeeInfoAPIController’:

You will get the methods for GET|POST|PUT and DELETE.

Including OData Libraries

This will add the reference of ‘System.Web.Http.OData’ assembly in the project.

Step 6: Since we are going to implement Pagination, we need to modify the GetEmployeeInfos method to return AsQueryable(). Open EmployeeInfoAPIController.cs and change the GetEmployeeInfoes() method as below:

//Modify the API Controller
[Queryable]
public IQueryable<EmployeeInfo> GetEmployeeInfos()
{
return db.EmployeeInfoes.AsQueryable();
}

The Queryable attribute specifies that the action method now supports the OData query syntax.

Step 7: In the Controller folder, add a new Empty MVC controller of name EmployeeInfoController. This will be an Index action method. Generate a new Index.cshtml view from this action method.

Step 8: Open Index.cshtml, add the following CSS and script reference:

<style type="text/css">
thead {
background-color:palegoldenrod;
}
</style>
<script src="~/Scripts/jquery-1.9.1.min.js"></script>
<script src="~/Scripts/knockout-2.2.1.js"></script>

Step 9: Add the following HTML in the page:

<div>
Top Records:
  <select id="lstpagesize">
   <option value="0">0</option>
   <option value="1">1</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
  </select>
  Skip Records:
  <select id="lstpageindex">
   <option value="0">0</option>
   <option value="1">1</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
  </select>
  <input type="button" id="btngetdata" value="Get Data" data-bind="click: EmpModel.GetRecord"/>
</div>
<table border="1">
<thead>
  <tr>
   <th>EmpNo</th>
   <th>EmpName</th>
   <th>Salary</th>
   <th>DeptName</th>
   <th>Designation</th>
  </tr>
</thead>
<tbody data-bind="template: { name: 'EmpData', foreach: EmpModel.Employees }">
</tbody>
</table>

The above code defines the Drop Down list using HTML <select> tag. The Button of name ‘btngetdata’ is bound with its click event to the EmpModel.GetRecord method. Similarly the table is declared with the header information. The table body is bound with the HTML template of name ‘EmpData’ and foreach parameter is passed with observable array of name EmpModel.Employees. We will be declaring the HTML template and the ViewModel in next forthcoming steps.

Step 10: Add the HTML template below the above HTML tag added on the Index.cshtml:

<script type="text/html" id="EmpData">
<tr>
  <td>
   <span   style="width:100px;"  data-bind="text: $data.EmpNo" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.EmpName" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.Salary" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.DeptName"  />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.Designation" />
  </td>
</tr>
</script>

The above HTML template defines table row with <span> in each table cell, each <span> is bound with the EmployeeInfo properties.

Step 11: Add the following script in the view below the HTML template:

<script type="text/javascript">
//The mode defining the observable array
var EmpModel = {
  Employees:ko.observableArray([])
};
//The method to get the records
EmpModel.GetRecord = function ()
{
  EmpModel.Employees([]);
  //Get the Page Records to be skipped based upon the page index
  var recordPerPage = $("#lstpagesize").find(":selected").val();
  var selectedPageIndex = $("#lstpageindex").find(":selected").val();
  //The URL for the WEB API
  //This selects the Top records and the page index
  //and skip those records from the data to be fetch
  var url = "/api/EmployeeInfoAPI?top=" + recordPerPage + '&$skip=' + (selectedPageIndex * recordPerPage) + '&$orderby=EmpNo';
  //Makes an ajax call
  $.ajax({
   type: "GET",
   url: url,
   success: function (data)
   {
    EmpModel.Employees(data);
   },
   error: function (err)
   {
    alert(err.status + "<--------->" + err.statusCode);
   }
  });
};
ko.applyBindings(EmpModel);
</script>

Editors Note: The jqXHR.success(), jqXHR.error(), and jqXHR.complete() callbacks have been deprecated in jQuery 1.8. To prepare your code for their eventual removal, use jqXHR.done(), jqXHR.fail(), and jqXHR.always() instead.

The method EmpModel.GetRecord makes an ajax call to WEB API. The url is as

var url = "/api/EmployeeInfoAPI?top=" + recordPerPage + '&$skip=' + (selectedPageIndex * recordPerPage) + '&$orderby=EmpNo';

This url has the OData query operator like top, $skip, $orderby. This query operators will now help to select the records from the server to implement pagination.

Step 12: Run the application and navigate to EmployeeInfo/Index. The following result will be displayed:

Now select Total Records as 2 and the Skip Records as 1. You will find the first two records from the above two tables will be skipped:

Conclusion

Download the entire source code of this article (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn