AspNet Web API OData makes it really simple to expose your IQueryable backend to be queried using the OData query syntax. Check out some samples here. If you are building using Entity Framework as your ORM (object-relational mapping) to talk to your database or just store all your data in-memory(Linq2Objects), you are in a good shape as you have an existing IQueryable. If you are talking to your own custom data source, you might not be lucky enough to have a LINQ provider or have a provider that has an incomplete implementation. If you are in this mess and are about to undertake the herculean task of implementing an IQueryable (don’t believe me – check out LINQ: Building an IQueryable provider series) – STOP now and thank me later.

Implementing a LINQ provider is hard. The query space that LINQ allows is huge and it is almost always easy to write a query that a provider cannot translate. Linq2Objects is an exception. But then, the Linq2Objects provider does not do any query translation in the truest sense. IQueryable is a powerful interface and if all you wanted to do is expose your backend for OData querying, it begs the question – is it really necessary? If you are building on top of AspNet Web API OData – the answer is NO. The query space that is covered by the OData query syntax is way smaller than LINQ. There are no complex projections ($select is very restricted), no nested queries to worry about, no complex joins and SelectMany’s, no GroupBy’s and aggregates etc. As a result, implementing the OData query space is easier than implementing a complete LINQ provider.

One of the places where Web API OData shines over WCF Data Services for building OData services is flexibility. Querying is no exception. Slap the [Queryable] attribute on your action returning IQueryable, you have the full power of OData querying – simplicity FTW. Your backend is not IQueryable, model bind the OData query to ODataQueryOptions, and, translate and “Apply” the query manually to your backend – flexibility FTW.

The rest of the post shows the second option in detail using a sample with NHibernate as the backend ORM layer.

NHibernate and HQL

NHibernate is an ORM layer on .NET platform. It is a .NET port of the Java ORM Hibernate. NHibernate uses a query language HQL (Hibernate Query Language) that is similar in appearance to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association.

The sample that I have uses NHibernateFluent to define the object-database mappings and is using SQLite as the backend store. Check out Customer.cs, CustomerMap.cs and CustomersSessionFactory.cs for the model and the mappings code.

ODataQueryOptions

ODataQueryOptions<T> was built to be used when the user wants to take manual control over the OData query. It then becomes his/her responsibility to validate and execute the query and return the appropriate results in the response. The following snippet of code from CustomersController gives you the idea,

public class CustomersController : ApiController
{
    public IEnumerable<Customer> GetCustomers(ODataQueryOptions<Customer> queryOptions)
    {
        // validate the query.
        queryOptions.Validate(_validationSettings);


        // Apply the query.
        IQuery query = queryOptions.ApplyTo(_db);
    }
}

Translating ODataQueryOptions

The ApplyTo in the previous snippet is where the interesting work happens. ODataQueryOptions contains the individual $filter, $orderby, $skip, $top options.  

public class ODataQueryOptions
{
    // .. other stuff ..

    public ODataQueryContext Context { get; }

    public FilterQueryOption Filter { get; }

    public OrderByQueryOption OrderBy { get; }

    public SkipQueryOption Skip { get; }

    public TopQueryOption Top { get; }
}

The ApplyTo method takes the individual query options and translates them to HQL – the Hibernate query language.

$skip and $top

Translating $skip and $top is a piece of cake. The corresponding query options give the parsed value i.e. an integer through the ‘Value’ property. I translate $skip to a SetFirstResult method call and $top to SetMaxResults method call.

query.SetMaxResults(topQuery.Value);

query.SetFirstResult(skipQuery.Value);  

$orderby

$orderby is translated to HQL’s “order by” clause in the NHibernateOrderByBinder class. OrderByQueryOption provides access to the parsed list of order by nodes through the property “OrderbyNodes”. For simplicity, I am only handling ordering by simple properties, but, you can see that it is really easy to extend it to nested properties.

stringBuilder.Append("order by ");
foreach (var orderByNode in orderByQuery.OrderByNodes)
{
    var orderByPropertyNode = orderByNode as OrderByPropertyNode;

    if (orderByPropertyNode != null)
    {
        stringBuilder.Append(orderByPropertyNode.Property.Name);
        stringBuilder.Append(
           orderByPropertyNode.Direction == OrderByDirection.Ascending ? " asc," : " desc,");
    }
    else
    {
        throw new ODataException("Only ordering by properties is supported");
    }
}

$filter

Translating $filter is the most interesting part and involves the most code. I translate $filter to NHibernate “where” clause in the NHibernateFilterBinder class. Web API OData provides the parsed AST (Abstract Syntax Tree) for the $filter option through the property “FilterClause” on the FilterQueryOption class. NHibernateFilterBinder is just a tree visitor that visits the AST and builds the “where” clause.

The uber “protected string Bind(QueryNode node)” method looks at the node type and directs the call to the node specific method where the real translation happens. For instance, this is how OData ‘Any’ queries are translated,

private string BindAllNode(AllNode allNode)
{
    string innerQuery = "not exists ( from " + Bind(allNode.Source) + " " + allNode.RangeVariables.First().Name;
    innerQuery += " where NOT(" + Bind(allNode.Body) + ")";
    return innerQuery + ")";
}

We have seen how easy it is to expose a non-IQueryable data source to be queried using the OData query syntax using AspNet Web API OData. You can find the complete sample hosted on aspnet codeplex samples.