ASP .NET MVC 4 WebApi: Manually handle OData queries

Question

I have a Web Service made using the WebAPI provided by ASP .NET MVC 4. I know that the layer on top of which WebAPI works automatically handles OData Queries (such as $filter, $top, $skip), but what if I want to handle the filtering by myself?

I don't simply return data from my database, but I have another layer which adds some properties, makes some conversions etc. So querying ALL of my data, converting them and returning them to the WebAPI class for OData filtering isn't just good enough. It's of course terribly slow, and generally a crappy idea.

So is there a way to propagate the OData query parameters from my WebAPI entry point to the functions I call to get and convert the data?

For example, a GET to /api/people?$skip=10&$top=10 would call on the server:

public IQueryable<Person> get() {
    return PersonService.get(SomethingAboutCurrentRequest.CurrentOData);
}

And in PersonService:

public IQueryable<Person> getPeople(var ODataQueries) {
    IQueryable<ServerSidePerson> serverPeople = from p in dbContext.ServerSidePerson select p;
    // Make the OData queries
    // Skip
    serverPeople = serverPeople.Skip(ODataQueries.Skip);
    // Take
    serverPeople = serverPeople.Take(ODataQueries.Take);
    // And so on
    // ...

    // Then, convert them
    IQueryable<Person> people = Converter.convertPersonList(serverPeople);
    return people;
}

Answer 1

I just stumbled across this old post and I'm adding this answer as it's now very easy to handle the OData queries yourself. Here's an example:

[HttpGet]
[ActionName("Example")]
public IEnumerable<Poco> GetExample(ODataQueryOptions<Poco> queryOptions)
{
    var data = new Poco[] { 
        new Poco() { id = 1, name = "one", type = "a" },
        new Poco() { id = 2, name = "two", type = "b" },
        new Poco() { id = 3, name = "three", type = "c" }
    };

    var t = new ODataValidationSettings() { MaxTop = 2 };
    queryOptions.Validate(t);

    //this is the method to filter using the OData framework
    //var s = new ODataQuerySettings() { PageSize = 1 };
    //var results = queryOptions.ApplyTo(data.AsQueryable(), s) as IEnumerable<Poco>;

    //or DIY
    var results = data;
    if (queryOptions.Skip != null) 
        results = results.Skip(queryOptions.Skip.Value);
    if (queryOptions.Top != null)
        results = results.Take(queryOptions.Top.Value);

    return results;
}

public class Poco
{
    public int id { get; set; }
    public string name { get; set; }
    public string type { get; set; }
}

Answer 2

The query from the URL gets translated into a LINQ expression tree which is then executed against the IQueryable your operation returns. You can analyze the expression and provide the results in any way you want. The downside is that you need to implement IQueryable which is not super easy. Take a look at this blog post series if you're interested: http://blogs.msdn.com/b/vitek/archive/2010/02/25/data-services-expressions-part-1-intro.aspx. It talks about WCF Data Services, but the filter expressions used by the Web API will be very similar.

Answer 3

One way With Web-api would be with customer message handler http://www.asp.net/web-api/overview/working-with-http/http-message-handlers

Write a custom handler like below:

public class CustomHandler : DelegatingHandler
    {
        protected override Task<HttpResponseMessage> SendAsync(
            HttpRequestMessage request, CancellationToken cancellationToken)
        {
            return base.SendAsync(request, cancellationToken).ContinueWith(
                (task) =>
                {
                    HttpResponseMessage response = task.Result;
                    var persons = response.Content.ReadAsAsync<IQueryable<Person>>().Result;
                    var persons2 = new List<Person>(); //This can be the modified model completely different
                    foreach (var item in persons)
                    {
                        item.Name = "changed"; // here you can change the data
                        //persons2.Add(....); //Depending on the results modify this custom model
                    }
                    //overwrite the response
                    response = new HttpResponseMessage<IEnumerable<Person>>(persons2); 
                    return response;
                }
            );
        }
    }

Register in global.asax.cs

Method in application class:

static void Configure(HttpConfiguration config)
 {
     config.MessageHandlers.Add(new CustomHandler()); 
 }

protected void Application_Start()
{
     ....
     .....
     //call the configure method
     Configure(GlobalConfiguration.Configuration);
 }

Answer 4

I did something like this with WCF Data Services and asp.net mvc 3.5 but it was a bit of a kludge.

The first step is to rewrite the path so that the skip and top options don't get applied twice, once by you and once by the runtime.

I did the rewriting with an HttpModule. In your BeginRequest method you'd have code like this:

HttpApplication app = (HttpApplication)sender;
if (HttpContext.Current.Request.Path.Contains(YOUR_SVC))
{
    if (app.Request.Url.Query.Length > 0)
    {
        //skip questionmark
        string queryString = app.Request.Url.Query.Substring(1) 
                    .Replace("$filter=", "filter=")
                    .Replace("$orderby=", "orderby=")
                    .Replace("$top=", "top=")
                    .Replace("$skip=", "skip=");

                HttpContext.Current.RewritePath(app.Request.Path, "", queryString);
    }
}

Then just examine the query string and pluck out the parameters you need.

if (HttpContext.Current.Request.QueryString["filter"] != null)
    var filter = HttpContext.Current.Request.QueryString["filter"] as string;

Then split the filter string and parse it into a sql statement or any other db commands. I was using NHibernate in my case. I was also able to limit what filter commands I supported which made things easier. I didn't do groupings for example. Mostly just the comparison operators.

There's a list of filter operators at OData.org. Split the string by "and" and "or" into individual clauses. Split each clause by a space and you should get a 3 element array with the property name in [0] the operator in [1] and the value in [2].

The clauses will be in terms of a Person but I'm assuming you'll be able to convert them into something that will pull the right results out of the db.

I ended up abandoning this approach since it won't work for POSTS. I had to write my own Linq provider but writing my own filter string parser made that easier to understand.