OData Querying Support in ASP.NET Web API

July 8, 2012  /  Leave a response

The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP,  Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years.  OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.

ASP.NET Web API supports  a subset of the OData protocol. We  can use below query interceptors of OData in ASP.NET Web API to query data returned by an action. The only important thing is Web API controller action should return data as IQueryable & action method should be annotated with [Queryable] attribute in System.Web.Http namespace.

• $top –  Retrieves a certain number of results.
• $skip – Skips over a certain numbers results & returns the remaining result set.
• $orderby – Retrieves result set in a certain certain order.
• $filter – Filter the result set returned

Let’s see above query interceptors in action. I have Product model in my ASP.NET MVC 4 solution.

namespace ODataQueryingDemo.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public string Category { get; set; }
    }
}

I have a ProductsController which a Web API controller. It has GetProducts() method returning list of products. Important point is GetProducts() method should return IQueryable<Product> & annotated with [Queryable] attribute, to enables OData querying support on the result set of GetProducts() method.

using ODataQueryingDemo.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;

namespace ODataQueryingDemo.Controllers
{
    public class ProductsController : ApiController
    {
        [Queryable]
        public IEnumerable<Product> GetProduct()
        {
            return new List<Product>
            { 
                new Product { Id=1, Name="EOS 1100D", Price =29990, Category="Cameras" },
                new Product { Id=2, Name="EOS 550D", Price =30995, Category="Cameras" },
                new Product { Id=3, Name="EOS 550D Kit", Price =34995, Category="Cameras" },
                new Product { Id=4, Name="EOS 5D", Price =127000, Category="Cameras" },
                new Product { Id=5, Name="ASP.NET MVC 3", Price =434, Category="Books" },
                new Product { Id=6, Name="REST in Practice", Category="Books" },
                new Product { Id=7, Name="Code Complete", Price =2328, Category="Books" },
                new Product { Id=8, Name="Lumia 900", Price =35487, Category="SmartPhones" },
                new Product { Id=9, Name="iPhone 4S", Price =45000, Category="SmartPhones" },
                new Product { Id=10, Name="Galaxy S3", Price =389000, Category="SmartPhones" }
            }.AsQueryable();
        }
    }
}

Let’s run the application to test it.  In order test these application I am not going to build any client application which consumes Products  API, I am going to simply test in browser.

I am entering the following URL in browser:  /api/products/

Above URL will return all the products returned by GetProducts() method.

URL: /api/products?$top=3

Above URL will return top 3 products in list of products returned by GetProducts() method.

URL: /api/products?$skip=5

Above URL will skip the first 5 products in result set returned by GetProducts() method  & returns the remain products.

URL: /api/products?$top=4&$skip=6

The combination of $top & $skip provides pagination. Above URL will skip the first 6 products in result set returned by GetProducts() method  & returns next 4 products.

URL: /api/products?$orderby=Name

Above URL will return products returned by GetProducts() method  in the ascending order of Name property of Product class.

URL: /api/products?$filter=Category eq ‘SmartPhones’

Above URL will return products returned by GetProducts() method  category of smart phones.

More information about $filter is available @ http://www.odata.org/documentation/uri-conventions#FilterSystemQueryOption

The current release of Web API  supports only above 4 query interceptors, Web API product team should provide support all the query interceptors supported by OData in next release of Web API hopefully.

The more info about OData URI conventions is available @ http://www.odata.org/documentation/uri-conventions.