Paging with ASP.NET Web API OData

Article
02/19/2013

A common requirement for web applications is to expose partial results from a larger set of data. Maybe you want your users to be able to browse through a collection of movies that they can stream. But you don't want to display all of the thousands of movie titles you have available, just the first twenty titles in the category the user is browsing through. This avoids overloading your users with information and avoids the significant performance and bandwidth costs associated with sending large chunks of data.

Now paging can be implemented in one of two ways:

Client-driven paging: The client decides how big of a page it wants to display and asks the server for a page of that size. So in our previous example, the client would be saying "Give me twenty comedies".
Server-driven paging: The client asks for a collection of entities, and the server sends back partial results as well as a link to use to retrieve more results. In our example, the client would be saying "Give me comedies" and the server would respond with "Here are twenty comedies and here's how to get more of them".

The good news is that the OData support we're adding to Web API adds built-in support for both of these options.

Client-driven paging

The way to implement client-driven paging with Web API is to take advantage of OData's query syntax and the $top query option. $top lets a client ask for a particular number of entities. So all the client would have to do is turn on Web API query support with this attribute on your action:

    1: [Queryable]
    2: public IQueryable<Movie> GetMovies

To get the first twenty comedies, you could then make a GET request for the following URI:

https://server/Movies?$filter=Genre eq 'Comedy'&$top=20

The $filter query option is narrowing down our query to only consider movies that have a category . Since we're not specifying an ordering on the movies, the server will infer a stable ordering based on the entity key so the results would be ordered by the movie ID in this case.

Now this only gets us the first page of comedies. If we want the next page of comedies, we can use the $skip query option to ignore the comedies we've already displayed. So:

https://server/Movies?$filter=Genre eq 'Comedy'&$skip=20&$top=20

Would display comedies 21-40, the next page we need to display.

A word of caution here: using $skip works great if your movie data isn't changing. But if you're inserting new movies into your database, it can cause movies to appear twice in your results. And if you're deleting movies, it can cause movies not to appear at all. If this is an issue for you, you should consider getting subsequent pages in a different way. You can use the properties you're ordering by and the value of these properties in the last entity of the current page to create a request that gets the next page. Let's demonstrate with an example. We've been ordering our movies by ID so far. So let's say we get the first page of comedies and the last comedy we get back has ID equal to 86. We could request the next page of comedies with a request for the following URI:

https://server/Movies?$filter=Genre eq 'Comedy' and ID gt 86&$top=20

This retrieves the first twenty comedies with an ID greater than 86, which is exactly what we need to display our second page. And even if new movies are inserted or movies are deleted, we'll avoid displaying the same movie twice or missing a movie.

Server-driven paging

In our previous examples, the client always needed to send "$top=20" to let the server know that it shouldn't send back more than twenty movies. But you can also choose to let the server control paging instead. You'll just need to slightly tweak your [Queryable] attribute like this:

    1: [Queryable(PageSize=20)]
    2: public IQueryable<Movie> GetMovies

This limits the number of movies to send back to the client to 20. If the query for this action yields fewer than or exactly twenty results, then nothing happens and the results are sent as if there were no paging. But if the query yields more than twenty results, then the server will only send back the first twenty results and add a next page link to the response body to give the client a way to get the next page of links. So for a request to this URI:

https://server/Movies?$filter=Genre eq 'Comedy'

Here's what the response body might look like:

    1: {
    2:   "odata.metadata":"https://localhost:23645/api/$metadata#Movies","value":[
    3:     {
    4:       "ID":17,"Title":"Six Days Seven Nights","ReleaseYear":1998,"Genre":"Comedy","Rating":2,"Popularity":6
    5:     },
    6:     ...
    7:     {
    8:       "ID":57,"Title":"Army of Darkness","ReleaseYear":1992,"Genre":"Comedy","Rating":9,"Popularity":9
    9:     }
   10:   ],"odata.nextLink":"https://localhost:23645/api/Movies?$filter=Genre%20eq%20'Comedy'&$skip=20"
   11: }

Notice that the next link that you see generated is based on $skip and is therefore subject to the same limitations as discussed earlier. The client can always choose to ignore this next page link and create one based on ordering instead.

Using the $inlinecount query option

So far, we've shown how clients can display pages of results in two different ways. It's fairly easy for clients to navigate to the next page by using the next page link or by generating one. And it's also fairly easy for clients to navigate to previous pages just be remembering the URI associated with the previous pages. But there is one additional piece of information that is very useful for paging in a web application: how many pages are there anyways?

This is where the $inlinecount query option comes in. $inlinecount can be applied to any query to get the server to send back the total number of entities matching the request. Let's see an example in action. For a request to this URI:

https://server/Movies?$filter=Genre eq 'Comedy'&$inlinecount=allpages

We'd get back the following response body if we had a page size limit on the server:

    1: {
    2:   "odata.metadata":"https://localhost:23645/api/$metadata#Movies","odata.count":"799","value":[
    3:     {
    4:       "ID":17,"Title":"Six Days Seven Nights","ReleaseYear":1998,"Genre":"Comedy","Rating":2,"Popularity":6
    5:     },
    6:     ...
    7:     {
    8:       "ID":57,"Title":"Army of Darkness","ReleaseYear":1992,"Genre":"Comedy","Rating":9,"Popularity":9
    9:     }
   10:   ],"odata.nextLink":"https://localhost:23645/api/Movies?$filter=Genre%20eq%20'Comedy'&$inlinecount=allpages&$skip=20"
   11: }

We now have a count in our response body. This count represents the total number of comedies in the server's database. The client can use this information to infer the number of pages that can be displayed, like this for example:

    1: var numberOfPages = Math.ceiling(count / pageSize);

In this case, we’d compute that since there are 799 comedies in our database and since we’re displaying 20 comedies per page, we’ll have 40 pages.

Note that server-driven paging only works by default with the OData format. Marking a Queryable action with the PageSize attribute will still limit the results for vanilla XML and JSON formatters, but it won’t add a next page link or a count. It’s possible to use the PageResult<T> class to add that information for other formats as well. You can even implement an action filter that converts a response into a PageResult to automatically enable similar functionality in other formatters. Look out for a future blog post detailing how to do that.

For more information about server-driven paging, take a look at this sample. It demonstrates implementing server-driven paging for a collection of movies with Web API’s OData support. If you’re new to writing OData services with Web API, I’ve posted a getting started guide over on the webdev blog to help get you up and running.

Paging with ASP.NET Web API OData

Client-driven paging

Server-driven paging

Using the $inlinecount query option

Additional resources