Working with OData Queries in ASP.NET Web API

OData protocol allows you to create RESTful data services by exposing URI end points. 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. To that end this article illustrates how to work with the OData support provided in the Web API, and how to write OData queries.

Introduction

OData protocol allows you to create RESTful data services by exposing URI end points. 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. To that end this article illustrates how to work with the OData support provided in the Web API, and how to write OData queries.

What is OData?

OData stands for Open Data Protocol. OData allows you to create REST based data services by exposing HTTP end points. In other words, you use standard URL syntax to query data residing on the server. OData queries consist of one or more Query Options. A query option is a sort of keyword that begins with $. For example, $top query option is used to indicate that top n records are to be fetched from the server. Just to show how an OData query looks, check out the following query:

http://localhost/api/customers?$top=10&orderby=country

The above OData query consists of two query options namely $top and $orderby. The query options are passed in the query string to a resource just like any other URL. Their use is quite obvious. The above query will return the top 10 records from a given result set and the results will be sorted by country in ascending order.

Web API Support for OData Queries

ASP.NET Web API supports OData queries with certain limitations. Note that OData support in Web API is still evolving and the supported feature set may change by the time the final version is released. The following OData query options are commonly used and are supported by Web API:

  • $top : Can be used to retrieve top n records from a data store.
  • $orderby : Can be used to sort the data in ascending or descending order.
  • $filter : Can be used to filter the data based on some condition.
  • $skip : Can be used along with $orderby to skip certain number of records.

In order to use OData queries in ASP.NET Web API you need to install Microsoft ASP.NET Web API OData NuGet package in an ASP.NET MVC 4 Web API project. So, first of all create a new ASP.NET MVC 4 Web API project in Visual Studio. Then select Project > Manage NuGet Packages menu option and search for Microsoft.AspNet.WebApi.OData. The following figure shows the said NuGet package ready to be installed.

Manage NuGet Package
Manage NuGet Package

This will install all the necessary assemblies that you need.

Creating a Sample Web API

Now let’s create a simple Web API that exposes data from the Customers table of the Northwind database. Begin by adding a new Entity Framework Data Model for the Customers table in the Models folder of the project (see below).

Add a New Entity Framework Data Model
Add a New Entity Framework Data Model

Next, open the Web API controller class (ValuesController) and change its name as CustomersController. Notice that the CustomersController class inherits from ApiController base class. Modify the CustomersController class as shown below:

public   class CustomersController : ApiController
{
      [Queryable]
      public IQueryable<Customer> Get()
      {
          NorthwindDbEntities db=new NorthwindDbEntities();
          return db.Customers;
      }
 
}

As you can see the CustomersController class contains a single method Get() that returns IQueryable collection of Customer objects. More importantly the Get() method is marked with the [Queriable] attribute. The [Queryable] attribute enables the OData support in Web API so that you can issue OData queries.

That’s all you need to do in your Web API to enable OData query support.

Issuing OData Queries from jQuery

Now let’s issue some OData queries to the Web API you just developed. Run the Web API project so that a browser window is opened and the default Web API page is shown. Enter the following queries in the browser address bar and observe the output returned in the browser for each query.

1. http://localhost:49174/api/customers?$top=3

2. http://localhost:49174/api/customers?$orderby=Country&$top=3

3. http://localhost:49174/api/customers?$orderby=Country desc&$top=3

4. http://localhost:49174/api/customers?$filter=Country eq ‘USA’

5. http://localhost:49174/api/customers?$orderby=Country&$skip=90

You will find that each of the queries returns results in XML format as shown in the following figure.

Each of the Queries Returns Results in XML Format
Each of the Queries Returns Results in XML Format

If you issue the same queries via jQuery code the data is returned as JSON and you can access the individual Customer objects just like any other JSON object.

The following view shows how OData queries can be issued using jQuery.

OData Queries can be Issued Using jQuery
OData Queries can be Issued Using jQuery

The main functionality of the view goes in the click event handler of the Select button. This event handler is shown below:

$("#Button1").click(function   () {
      var url = "api/customers?";

      if ($("#Text1").val() != '') {
          url += "$top=" + $("#Text1").val() + "&";
      }

      if ($("#Text2").val() != '') {
          url += "$filter=Country%20eq%20'" + $("#Text2").val() +   "'&";
      }

      if ($("#Select1").val() != '') {
          url += "$orderby=" + $("#Select1").val();
      }

      $.getJSON(url, LoadCustomers);
});

As you can see the click event handler of the Select button essentially forms the same OData queries as before but this time the queries are issued using the $.getJSON() method of jQuery. If you wish you can also use $.ajax() instead of $.getJSON() method. The $.getJSON() method accepts the URL that is supposed to be requested (OData end point in this case) and a callback function to handle the returned data.

The callback function LoadCustomers looks as follows:

function   LoadCustomers(data) {
      $("#customerTable").find("tr:gt(0)").remove();
      $.each(data, function (key, val) {
          var tableRow =  '<tr>' +
                          '<td>' + val.CustomerID + '</td>' +
                          '<td>' + val.CompanyName + '</td>' +
                          '<td>' + val.ContactName + '</td>' +
                          '<td>' + val.Country + '</td>' +
                          '</tr>';
          $('#customerTable').append(tableRow);
      });
 }

The LoadCustomers() function receives the Customer JSON objects as the data parameter. The function essentially fills the data in a table with ID customerTable.

Summary

ASP.NET Web API offers support to OData queries. OData query support in Web API comes from the [Queryable] attribute. Currently the OData support is still in an evolving state and many more features may be added before the final release. OData queries are a part of the supported features that allows you to query data using URIs.

About the Author:

Bipin Joshi is a blogger and author who writes about apparently unrelated topics – Yoga & technology! A former Software Consultant and Trainer by profession, Bipin has been programming since 1995 and working with .NET framework ever since its inception. He has authored or co-authored half a dozen books and numerous articles on .NET technologies. He has also penned a few books on Yoga. Having embraced the Yoga way of life he now writes about Yoga, life and technology on his website. He can also be reached there.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read