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 Absolutely Awesome jQuery Cookbook. Covers jQuery 1.11, 2.1 and jQuery UI 1.11.

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

Step 5: To do this, right click on the Project and select the Manage NuGet package and select and install ‘Microsoft ASP.NET Web API OData’ package as below:

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 GetEmployeeInfosmethod 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

Exposing OData queries over ASP.NET WEB API is very easy to implement. Enabling querying of data like this makes the API really powerful across various possible clients. Data filters, pagination can be easily implemented to reduce huge amount of data fetched from the server and hence can save the bandwidth.

Download the entire source code of this article (Github)