Exposing IQueryable/oData Endpoints With Web API

This is a follow on from my post on Web API and the Entity Framework. In that post, I showed a couple of approaches to dealing with JSON serialization problems in the Visual Studio 11 beta. Now I want to look at returning IQueryable from Web API methods.

IQueryable allows you to do what it says on the box: return an object that can be queried from the client. In other words, you can pass through arguments that tell the server what data to retrieve. This is a hot topic on the Web, with some people strongly against the idea and some strongly for it. The argument is between convenience (look how easy it is to get any data I want!) and security/architecture (look how easy it is for someone else to get any data they want).

I don’t have strong views either way. I share the concerns of those who worry about leaving client layers free to bombard the data layer with inappropriate (and potentially dangerous) queries, but I also like the convenience of being able to shape my queries from the client—especially given the Web API’s (partial) support of the oData specification. (For those unfamiliar with oData, it allows you to use querystring arguments to modify the query at the server, e.g., $top=10 will become .Take(10) in the EF query).

If I don’t use IQuerable, I will need to write lots of different methods to allow for different queries (e.g., in cocktails-r-us, I need to search for cocktails by beverage, non-liquid ingredient, name, id, etc.). Here is a simple example from my demo project, with two methods:  one returning an IEnumerable of BeverageTypes, the other a single BeverageType by id:

original version of code

If I want to get an individual BeverageType, I make a get request along these lines: http://%5Bmysite%5D/api/beveragetype/2. Here is the Firebug output from such a request:

output from non-oData request

If I switch to IQueryable as the return type, however, I can supply both queries from a single method (note the addition of ‘AsQuerable()’ at the end of the method return):

IQueryable version of the code

Now I can write my oData query as “http://%5BMySite%5D/api/beveragetype?$filter=id eq 2“, so I no longer need my separate specialized method.

screenshot of oData request and result

Let’s try and simplify the method. The oData specification allows us to expand associations using $expand, so let’s remove the .Include(“Beverages”) call from our method and pass that through on the querystring as follows: http://%5BMySite%5D/api/beveragetype?$filter=id eq 2&$expand=Beverages.

Here is the new code:

code without include

And here is the result… not quite what we were hoping for:

result of $expand - no include

It turns out that the Web API does not support $expand…. And I rather hope it never does. If Web API supported $expand, then my users would be able to create huge queries with far too many joins. In cocktails-r-us, I only want to return all the details of a cocktail (ingredients, comments, ratings, related cocktails, etc.) for one cocktail at a time. I don’t want users to be able to join all those tables in one massive query. So, that’s the upside. The downside is that I have to go back to using multiple methods, but even then I should only need two:  one to get a specific cocktail (or, in this case, BeverageType) by ID, the other to return lists of them by whatever criteria the client prefers.

final version of two methods

Since I can deal with security concerns by forcing my clients to authenticate (and only giving access to trusted developers in the first place), and that leaves me with only one concern: will my client developers write ill-advised queries? They might, for example return too many rows at once instead of paginating through the data as I would prefer. Fortunately, there is a solution: the [ResultLimit(n)] attribute. This restricts the number of items returned. So now our remote users will have to page through the data rather than return it all at once.

ResultLimit attribute

If we examine the output in Firefox/Firebug, you can see that only 5 rows are returned even though the query requested all the rows:

output with limit in place

ResultLimit is not a perfect solution. It only restricts the number of items returned from the method, not the number retrieved from the database. Here is what’s happening on the server:

Results on server

However, since the remote user won’t be able to get the data they want the easy way, they will be forced to write oData pagination queries like “http://%5Bsite%5D/api/beveragetype?$skip=5&$top=5&$orderby=Type desc” which would give them the following output on the client:

oData paginated query

I understand why people are nervous about exposing IQueryable, and I wouldn’t do it in a completely open way where anyone’s code can access my data without authentication, but I love its openness and flexibility and the way it works so easily with Web API.

Kevin Rattan

For related information, check out this course from Learning Tree: Building Web Applications with ASP.NET MVC.

6 Responses to “Exposing IQueryable/oData Endpoints With Web API”


  1. 1 Tony Deacon April 26, 2012 at 5:10 am

    I think the concept of using OData with Web API is great but I think people need to be aware that deferring filters to OData means that large sets of data may be pulled back from the db only to be discarded at the OData layer which seems wasteful on resources and some might say works against the Linq idea of only returning required data.

    For example,
    doing Get /api/beveragetype?$top20
    ….
    return db.BeverageTypes.ToList().AsQueryable();

    will pull back all the BeverageTypes. OData layer then filters on that result set taking the top 20.

    whereas
    doing Get /api/beveragetype
    …..
    return db.BevarageTypes.ToList().Take(20);

    will only return the top 20 BeverageTypes from the db.

    It’s definitely worth profiling the sql requests if you are unsure what is happening during the call I think.

    • 2 Tony Deacon April 26, 2012 at 6:21 am

      Apologies! It turns out the example code I cut n pasted was preventing deferred execution and hence the whole result set was being returned.

      • 3 Kevin Rattan, Learning Tree April 26, 2012 at 11:54 am

        Hi Tony,

        Your point is a good one, however.

        There is a danger of too much data being lifted from the database. I initially experimented with limiting the return by specifying a fixed .Take(20), but unfortunately that doesn’t work, for the same reason you outlined – the problem being that the oData filter runs against the 20 returned rather than determining which 20 are returned.

        For myself, I am going to compromise and expose full oData endpoints where there is too little data for it to be an issue – for example, there are only so many beverageTypes, and they’re small – but use arguments to specify pagination where it would be too big a hit (e.g cocktails).

        Cheers,

        Kevin


  1. 1 Analyzing Queries with a SQL Profiler « Perspectives on .NET Programming From Learning Tree International Trackback on May 30, 2012 at 9:16 am
  2. 2 Web API OData support via IQueryable – It’s not just magic | Here and there Trackback on July 25, 2013 at 3:55 pm
  3. 3 Suporte a OData pela Web API usando IQueryable – Não funciona num passe de mágica | Here and there Trackback on July 25, 2013 at 4:02 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Learning Tree International

.NET & Visual Studio Courses

Learning Tree offers over 210 IT training and Management courses, including a full curriculum of .NET training courses.

Free White Papers

Questions on current IT or Management topics? Access our Complete Online Resource Library of over 65 White Papers, Articles and Podcasts

Enter your email address to subscribe to this blog and receive notifications of new posts by e-mail.

Join 29 other followers

Follow Learning Tree on Twitter

Archives

Do you need a customized .NET training solution delivered at your facility?

Last year Learning Tree held nearly 2,500 on-site training events worldwide. To find out more about hosting one at your location, click here for a free consultation.
Live, online training

Follow

Get every new post delivered to your Inbox.

Join 29 other followers

%d bloggers like this: