ASP.NET Web API Pagination using OData operators

Posted by: Mahesh Sabnis , on 5/12/2014, in Category ASP.NET
Views: 56377
Abstract: This article demonstrates how to implement Pagination & Data Filters in ASP.NET Web API using OData and Knockout.js

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,

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 GetEmployeeInfos method to return AsQueryable(). Open EmployeeInfoAPIController.cs and change the GetEmployeeInfoes() method as below:

//Modify the API Controller
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 {
<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:

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>
  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>
  <input type="button" id="btngetdata" value="Get Data" data-bind="click: EmpModel.GetRecord"/>
<table border="1">
<tbody data-bind="template: { name: 'EmpData', foreach: EmpModel.Employees }">

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">
   <span   style="width:100px;"  data-bind="text: $data.EmpNo" />
   <span style="width:100px;"  data-bind="text: $data.EmpName" />
   <span style="width:100px;"  data-bind="text: $data.Salary" />
   <span style="width:100px;"  data-bind="text: $data.DeptName"  />
   <span style="width:100px;"  data-bind="text: $data.Designation" />

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 = {
//The method to get the records
EmpModel.GetRecord = function ()
  //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
   type: "GET",
   url: url,
   success: function (data)
   error: function (err)
    alert(err.status + "<--------->" + err.statusCode);

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(),, 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:



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)

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

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!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

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

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Mohammad on Monday, May 12, 2014 9:48 AM
Nice article, however it would be nice if you could make a simple demonstration without using knockout js, and do pagination using forward and backward buttons.
Comment posted by JLuis Estrada on Wednesday, May 21, 2014 5:30 PM
This is far from a real-life example. Please stop doing that
Comment posted by Limo on Friday, May 23, 2014 10:28 AM
Real-life or not this article serves its purpose well! Nice and simple.
Comment posted by Nikolai Manek on Sunday, May 25, 2014 1:26 PM
Very nice article. You just saved me an hour of work.
Comment posted by Tohid Azizi on Friday, May 30, 2014 8:24 AM
Thank you for the fantastic article. Very nice.
PS: You have a broken link man! Check this link, it doesn't work:
Comment posted by Steve5877 on Saturday, June 28, 2014 11:27 AM
Not sure why, but "top" isn't working. "skip" works fine, but I keep getting all of the records after the skip back.  I tried switching the order of the skip and the top but same result.
Comment posted by Steve5877 on Saturday, June 28, 2014 12:14 PM
Found it. You're missing a $ before the word top in the script.
Comment posted by MAhesh Sabnis on Wednesday, November 12, 2014 3:36 AM
The article provides  guidelines for implementations with OData. Further as per the requirements of the project/application on which you are working, it can be modified.

Thanks a lot to all readers.
Mahesh Sabnis