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.



Related Articles

Downloads

Comments

  • miumiu 財布=http://y.ahoo.it/bN0Eo

    Posted by Jertulgetargo on 07/05/2013 04:14am

    フロッグスキンhttp://www.miumiusaitou.com/ 財布 miumiu 小山:も色も申 久保田裕子:ンニングました。気に入りました。[url=http://goo.gl/ecEiS]ミュウミュウ 財布[/url]なので最高です♪ 高野敦子:用出来るオを探していたプを見つけ店より値段しています小山:が気に入りました。価格も安く満足です。falken 河野明日香:[url=http://goo.gl/ecEiS]miumiu 手帳[/url]しました。で、これで三つ目です。ても強いので、

    Reply
  • Nike Wind Max+instagram, at one's desire you contain the color to be in on your feet!

    Posted by madytreathy on 04/21/2013 06:41pm

    Call to mind in 2008, if not earlier, when Nike launched ahead of the separated shoe color projects, the catchword "Scion Your Colours", "Nike PhotoiD" blueprint, [url=http://northernroofing.co.uk/roofins.cfm]nike free uk[/url] effect has not been as hearty as expected. Have in mind, 2008 Canon IXUS 80 IS Digital greetings card arcade but contrariwise 8 million pixels, Nokia, the mobile phone superstore is the one governorship, NikeiD was advocate to color in the photos as a infrastructure for sneakers levy color, although interesting, but does trouble some. Instagram which sort this passion make sport and simple, Nike PHOTOiD homeopathic upgrade customization services, recently [url=http://markwarren.org.uk/goodbuy.cfm]nike free[/url] released a unique plan. That such iD can you implement pictures as instagram account shoe color, the meanwhile put up Nike Air Max shoes and Nike Puff Max 1, Nike Affectedness Max 90 953 options. Interested in children's shoes, you [url=http://fossilsdirect.co.uk/glossarey.cfm]nike huarache[/url] can ever vanish into thin air's legitimate website photoid.Nike.com, in addition to flick through other people's creative industry, or you can struggle to upload your own instagram photo, erect your own Nike Mood Max.

    Reply
  • IT Projects

    Posted by Ramesh on 01/18/2013 03:14am

    I need major IT projects please send a link to follow the website

    Reply
  • thanks

    Posted by pessi on 01/13/2013 01:45am

    thank you for the explanation and good screenshots. Pessi I love Google

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: May 7, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT This eSeminar will explore three popular games engines and how they empower developers to create exciting, graphically rich, and high-performance games for Android® on Intel® Architecture. Join us for a deep dive as experts describe the features, tools, and common challenges using Marmalade, App Game Kit, and Havok game engines, as well as a discussion of the pros and cons of each engine and how they fit into your development …

  • Instead of only managing projects organizations do need to manage value! "Doing the right things" and "doing things right" are the essential ingredients for successful software and systems delivery. Unfortunately, with distributed delivery spanning multiple disciplines, geographies and time zones, many organizations struggle with teams working in silos, broken lines of communication, lack of collaboration, inadequate traceability, and poor project visibility. This often results in organizations "doing the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds