Implementing Sorting and Paging in the Web API Using OData Queries

The client displaying data returned by a Web API may want to implement sorting or paging on the data being returned. Although there can be different ways to implement these features, a simple technique is to use OData support offered by the Web API. This article shows how to call a Web API by using a client-side script and also shows how to implement Ajax-driven sorting and paging.

Defining the Requirements

In the example discussed in this article, you will meet the following requirements:

• The data to be displayed is returned by a Web API.
• The data should be displayed in an HTML table.
• The column headings should be clickable. Clicking a column should sort the data shown in the table.
• The sorting should be persistent and bidirectional. Clicking a column header should toggle the sort order between ascending and descending. The sort order must be persistent even when the page index changes.
• The bottom of the table should display a pager row with page numbers. Clicking a page should display only the records belonging to that page.
• The sorting as well as paging should be implemented by using Ajax calls.

Building Multi-device Apps with Apache Cordova and Xamarin in Visual Studio

Download Now

Figure 1 shows the final outcome of the example discussed in this article.

Figure 1: The completed table

As shown in Figure 1, the column headers are clickable hyperlinks. They don't point to any specific resource. They are simply used to indicate a clickable heading. The same holds true for hyperlinks shown in the pager row of the table. The click event raised by a hyperlink is trapped by using jQuery code and the form is submitted to the server programmatically for further action.

Creating the Model

To begin developing this example, create a new ASP.NET Web Application by using a Web API project template (see Figure 2).

Figure 2: Creating a new project

Then, right-click the Models folder and add a new ADO.NET entity data model for the Customers table of the Northwind database. Figure 3 shows the Customer entity class in the Visual Studio designer.

Figure 3: The Customer entity class

In addition to the Customer entity class, you need an auxiliary class: SortingPagingInfo. This class contains all the settings used by the sorting and paging features, such as the field on which the data is to be sorted and the total number of pages. The SortingPagingInfo class is shown below:

public class SortingPagingInfo
{
   public string SortField { get; set; }
   public string SortDirection { get; set; }
   public int PageSize { get; set; }
   public int PageCount { get; set; }
   public int CurrentPageIndex { get; set; }
}

As you can see, the SortingPagingInfo class consists of five properties: SortField, SortDirection, PageSize, PageCount, and CurrentPageIndex. These property names are self explanatory and therefore are not discussed here.

Creating the Web API

This example uses OData support provided in the Web API. Data protocol allows you to create RESTful data services by exposing URI end points. The ASP.NET Web API supports OData queries so that your data can be consumed by the client over HTTP with the help of OData query options. If you are not familiar with this feature, you may consider reading this article first.

Once the model is ready, right-click the References folder and select the "Manage NuGet Packages" option. In the Manage NuGet Packages dialog, search for WebApi.OData and install the "Microsoft ASP.NET Web API 2.2 for OData v1.3" package. Along with the installation, other dependencies also will be added for you.

Figure 4: Installing the "Microsoft ASP.NET Web API 2.2 for OData v1.3" package

Then, add a new Web API controller in the Controllers folder and name it CustomerController. This controller class is shown below:

public class CustomerController : ApiController
{
   [EnableQuery]
   public IQueryable<Customer> GetCustomers()
   {
      NorthwindEntities db = new NorthwindEntities();
      eturn db.Customers;
   }
}

As you can see, the CustomerController class contains only one method: GetCustomers(). This method is invoked from the client through a GET request and returns the required data. Notice that the GetCustomers() method is decorated with the [EnableQuery] attribute. The [EnableQuery] attribute enables OData support for the Web API. The return type of the method is IQueryable of Customer objects. Inside, it simply returns all the data from the Customers DbSet back to the caller. This is all you need to do in the Web API.

Creating the HomeController

Open the HomeController from the Controllers folder and modify the Index() action method as shown below. The Index() action sets the default values for the sorting and paging features.

public class HomeController : Controller
{
   public ActionResult Index()
   {
      using (NorthwindEntities db = new NorthwindEntities())
      {
         SortingPagingInfo info = new SortingPagingInfo();
         info.SortField = "CustomerID";
         info.SortDirection = "asc";
         info.PageSize = 10;
         info.PageCount = Convert.ToInt32(Math.Ceiling((double)(db.Customers.Count()
            / info.PageSize)));
         info.CurrentPageIndex = 0;
         ViewBag.SortingPagingInfo = info;
         return View();
      }
   }
}

The Index() action method creates an instance of the SortingPagingInfo class created earlier and sets its properties, such as SortField, SortDirection, PageSize, PageCount, and CurrentPageIndex. The SortingPagingInfo object is passed to the view by using a ViewBag property so that these values can be utilized on the view.

Creating the Index View

Now comes the important part: consuming the Web API and implementing the sorting and paging features. Add the Index view and write the following code in it:

<h1>List of Customers</h1>
@{
    SortingPagingInfo info = ViewBag.SortingPagingInfo;
}
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
   @Html.Hidden("SortField", info.SortField)
   @Html.Hidden("SortDirection", info.SortDirection)
   @Html.Hidden("PageCount", info.PageCount)
   @Html.Hidden("PageSize", info.PageSize)
   @Html.Hidden("CurrentPageIndex", info.CurrentPageIndex)
   <table border="1" cellpadding="10">
      <tr class="headerRow">
         <th><a href="#" data-sortfield="CustomerID"
            class="header">CustomerID</a></th>
         <th><a href="#" data-sortfield="CompanyName"
            class="header">CompanyName</a></th>
         <th><a href="#" data-sortfield="ContactName"
            class="header">ContactName</a></th>
         <th><a href="#" data-sortfield="Country"
            class="header">Country</a></th>
      </tr>
      <tr class="pagerRow">
         <td colspan="4">
            @for (var i = 0; i < info.PageCount; i++)
            {
               <a href="#" data-pageindex="@i"
                  class="pager">@(i + 1)</a>
            }
         </td>
      </tr>
   </table>
}

The preceding code grabs the SortingPagingInfo object passed through the ViewBag and transfers its properties into five hidden fields. This is required because these values need to be accessed from jQuery code (discussed later). An HTML table is used to display the data. Notice how the table headers are shown. Each column header consists of an anchor element whose data-sortfield custom data attribute is set to the column name it represents. This data-sortfield attribute is used to decide the sorting order when a user clicks on the header. Also, notice that all the header anchor links have a header CSS class associated with them. This class is used as a selector in the jQuery code.

The pager row simply displays anchor elements based on the PageCount. Notice that each pager link stores its index in a data-pageindex attribute and that the table row has the pagerRow CSS class.

Next, add a script reference to the jQuery library and also add an empty script block.

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
   ...
</script>

Then, add a JavaScript function, GetData(), inside the <script> block. This function calls the Web API and retrieves the required data.

function GetData()
{
   
  var sortfield = $("#SortField").val();
   var sortdirection = $("#SortDirection").val();
   var currentpageindex = $("#CurrentPageIndex").val();
   var pagesize = $("#PageSize").val();
   var url = "/api/customer";
   var query = "";
   switch (sortfield) {
      case "CustomerID":
         query = (sortdirection == "asc" ? "$orderby=CustomerID asc" :
            "$orderby=CustomerID desc");
         break;
      case "CompanyName":
         query = (sortdirection == "asc" ? "$orderby=CompanyName asc" :
            "$orderby=CompanyName desc");
         break;
      case "ContactName":
         query = (sortdirection == "asc" ? "$orderby=ContactName asc" :
            "$orderby=ContactName desc");
         break;
      case "Country":
         query = (sortdirection == "asc" ? "$orderby=Country asc" :
            "$orderby=Country desc");
         break;
   }
   query += "&$skip=" + (currentpageindex * pagesize) + "&$top=" + pagesize;
   url += "?" + query;
   $.getJSON(url, function (data) {
      $("table").find('tr').slice(1, -1).remove();
      for (var i = 0;i<data.length;i++)
      {
         var html = '<tr>';
         html += '<td>' + data[i].CustomerID + '</td>';
         html += '<td>' + data[i].CompanyName + '</td>';
         html += '<td>' + data[i].ContactName + '</td>';
         html += '<td>' + data[i].Country + '</td>';
         html += '</tr>';
         $(".pagerRow").before(html);
      }
   });
}

The GetData() function grabs the values from the SortField, SortDirection, CurrentPageIndex, and PageSize hidden fields. As mentioned earlier, you will be sending an OData query to the Web API to retrieve data. This query needs to have three parameters: $orderby, $skip, and $top.

The switch block determines and adds the $orderby parameter of the OData query. Notice how the "asc" and "desc" clause is being added. After the switch block, the $skip parameter is added by calculating its value as (currentpageindex * pagesize). Finally, the $top parameter is set to the value of the pagesize variable. This way, the Web API has everything it needs to sort and page the data under consideration.

The actual Ajax call to the Web API is made by using $.getJSON() of jQuery. The first parameter of $.getJSON() is the URL pointing to the Web API: /api/customer. The second parameter is a callback function that gets invoked when the call is complete.

The callback function receives the Customer data in the form of an array. Before adding this data to the table, the existing data (if any) is removed by slicing the table. This is done by using the slice() method. A for loop iterates through the data and adds a table row filled with the Customer data to the table by using the before() method.

The GetData() function is called from the ready() method of jQuery. This is shown below:

$(document).ready(function () {
   GetData();
   $(".header").click(function (evt) {
      var sortfield = $(evt.target).data("sortfield");
      if ($("#SortField").val() == sortfield) {
         if ($("#SortDirection").val() == "asc") {
            $("#SortDirection").val("desc");
         }
         else {
            $("#SortDirection").val("asc");
         }
      }
      else {
         $("#SortField").val(sortfield);
         $("#SortDirection").val("asc");
      }
      evt.preventDefault();
      GetData();
   });
   $(".pager").click(function (evt) {
      var pageindex = $(evt.target).data("pageindex");
      $("#CurrentPageIndex").val(pageindex);
      evt.preventDefault();
      GetData();
   });
});

As soon as the page loads in the browser, you need to display the first page of data. So, a call to GetData() is made immediately inside the ready() method.

A class selector is used to retrieve all the header anchor elements (header CSS class) and a click event handler is wired to them. The click event handler basically checks the header element that was clicked and accordingly sets the SortField hidden field. This is done by using the data-sortfield custom data attribute of the anchor element under consideration. Notice that if a header element is clicked multiple times, the SortDirection hidden field toggles its value between asc and desc. Then, GetData() is called to get the data as per the new sort order.

On similar lines, the click event handler of pager hyperlinks grabs the data-pageindex custom data attribute and sets the CurrentPageIndex hidden field. GetData() is called again so that the new page of data can be displayed.

That's it! Run the application and test whether sorting and paging works as expected.

Summary

The data returned from the Web API is often displayed in a tabular format. You may need to provide sorting and paging for such a table. By using OData support offered by the Web API, client-side script, and Ajax calls, you easily can implement persistent and bidirectional sorting as well as paging. This article illustrated how this can be accomplished.

Extra Reading Links

• Working with OData Queries in ASP.NET Web API
• Implementing Sorting and Paging in ASP.NET MVC (article will be published later)