Using OData query syntax with Web API

So I have was just messing around with a Web API and thought "I had better add some kind of filtering support here". I was vaguely aware of OData and knew that OData was the way to do filtering, sorting etc with REST APIs (.net Web API in my case), but had never looked into it.

When I did look into it, I have to say ... it blew my mind! It is soo easy to do and gives you so much power over your data. 

In the spirit of trying to 'learn in public', I've captured the main details here.

What is OData query syntax?

OData query syntax is a standard way of querying RESTful APIs using a pre-defined syntax that allows the calling client to define the sort order, filter parameters and pages of data that is returned.

The full syntax can be found in section 5 of the OData Version 4.0 Part 2 specification but here are some quick examples of how the syntax is used:

  • http://host/service/Products?$filter=Name eq 'Milk': Returns all products where the name is equal to 'Milk'
  • http://host/service/Products?$filter=Name eq 'Milk' and Price lt 2.55: Returns all products where the name is equal to 'Milk' and the price is less than 2.55
  • http://host/service/Products?$top=10&$skip10: Returns items 11>21. The $top system query option requests the number of items in the queried collection to be included in the result. The $skip query option requests the number of items in the queried collection that are to be skipped and not included in the result
  • http://host/service/Products?$orderby=Name desc,Id: Returns all products ordered by the Name in descending order, then ordered by Id
  • http://host/service/Products?$filter=Enabled eq true: Returns all products where the Enabled field (which is a boolean) is true
  • http://host/service/Products?$filter=substringof('Martin', Name): Returns all products the Name field contains the text Martin

How to use OData query syntax in Web API?

This is where things get really awesome - it is super, super easy to change a regular Web API controller to support OData query syntax - once you know how to do this, you'll never not do it!

If we assume we are starting from a regular Web API project (File > New Project > Web Application > Web API), you first need to add a scaffolded controller which you can do by following these steps:

  1. Add a model class (I'm using the typical 'Person' class with Id, First Name, Last Name, Age etc)
  2. Right-click the 'controllers' folder > Add > New Scaffolded item > Web API 2 Controller with actions, using Entity Framework > Model = Person

NOTE: You do not need to choose the 'Web API 2 OData Controller...' option, you can add the query syntax to any controller

Once you've setup your controller, you should end up with a simple controller which has a default GET action that looks a little like this:

// GET: api/People
public IQueryable<Person> GetPeople()
return db.People;

This action will simply return all the rows in the 'People' table of the database with no ability to filter, sort etc. To add full OData query support, you need to make a few changes:

  1. Add the Microsoft.Aspnet.OData package via nugget ... simply pop this into you 'Package Manager Console': Install-Package Microsoft.AspNet.Odata
  2. Add this using statement: using System.Web.OData;
  3. Add the[EnableQueryAttribute]attribute to your GetPeople action.
  4. Add AsQueryable(); to your return line so it looks like this: return db.People.AsQueryable();

Your finished code will look something like this:

// GET: api/People
public IQueryable<Person> GetPeople()
return db.People.AsQueryable();

That is all there is to it! .... now you have this setup, you can use the full OData syntax to query sort etc without any extra code.

Comments (5)

  1. bientity says:

    it is awesome and very useful, thanks

  2. Andrew Polden says:

    As useful as post this is, there appears to be a problem with paged querying. The response from the query:
    GET http://host/service/Products?$top=10&$skip10
    does not return the total number of records. I would have expected a kind of envelope in the response with that information, of the sort that PageResult provides from using an ODataController

    1. Martin Kearn says:

      Hi Andrew, There is no error here but you are right to say that this does not include the total. In this syntax you are just asking for the top 10 results after the first 10 (i.e. results 11>21). I’m not sure how you would return just a total count – I’ll look into it and update the article if I find an answer.

  3. Nick says:

    I needed to add an “=” before the skip value in order to get it to work.


    1. Gregg Gianopulos says:

      $count=true will cause the total count of records the query applies to, (not just the # returned as controlled by $top).

Skip to main content