If you are using the SharePoint REST API (listdata.svc) to return non-trivial amounts of data you should definitely consider implementing some form of paged access to the data; not only to reduce the burden on the server but also to improve the overall user experience. Fortunately with the SharePoint REST API this is pretty straight forward to do.

References

• ADO.NET Data Services v1.5 CTP1 – Server Driven Paging
• Open Data Protocol by Example
• 2.2.3.6.1.9 Skip Token System Query Option ($skiptoken)
• Supported OData Query Options

The REST API offers two types of paged access to data, both achieve the same result in different ways and which option you choose depends on a number of factors, including the schema of your data model and the type of pagination controls you’d like to display.

In this article;

• Type of Pagination Control
• Server Driven Paging
• Client Driven Paging
• Conclusion

Listdata.svc has a fixed upper limit on the number of items returned.

The listdata.svc component enforces a restriction on the maximum number of items returned by the interface, this is implemented by the implicit server driven paging mechanism and is set at 1000 items.

You cannot return more than 1000 items with a single request using the SharePoint REST API.

Types of Pagination Control

So, there are many different ways of offering pagination controls to a user, but essentially they fall into 2 rough categories;

Rich

In this category you’ll have next and previous controls and a number of direct access links to specific pages of data, with text labels of the “Showing x to y of z entries” variety.

Basic

In this category you’ll pretty much just have next and previous controls, perhaps with text labels of the “Showing x of y entries” variety.

Server Driven Paging

Server driven paging uses a paging model which is determined by the data returned itself rather than a numerical based paging system. To clarify, server driven paging is a function of the REST queries;

• Ordering sequence (specified by the $orderby query option)
• Continuation token (specified by the $skiptoken query option)

If you’re something of a DB head, this is akin to the indexed sequential access method.

As mentioned, the listdata.svc implementation will return no more than 1000 entries in response to a REST query. If a query yields more than 1000 entries a next link element is returned in the response.

To demonstrate, the image below shows a typical REST request, the final result-set of this query yields 2000 entries, but the REST API returns only 1000 entries and includes the next link URL which can be used to return the next page of results.

Notice that the $orderby query option sets the ordering sequence to Title ascending, also notice the last entry returned has a Title value of ‘Task 1899’ and an Id value of 1940. Now, the __next property shows a URL which includes the $skiptoken value of ‘Task 1899’,1940.

According to the ODATA specification, the $skiptoken value identifies the starting entry into a collection, of the data returned. However listdata.svc implements this starting point as 1 entry past the entry identified by the skiptoken, i.e.  skiptoken+1.

Further, the specification states that the values specified using $skiptoken must correlate with the ordering specification, which means that your $skiptoken values must relate to the columns used to order the query results, i.e. the columns used in the $orderby query option. The specification states that if no $orderby option is used, the data set MUST be fully ordered by the data service itself and therefore that your $skiptoken value must correlate with that ordering sequence.

The SharePoint REST API (listdata.svc) adds an implicit ordering sequence using the ID value of an entry, even when you specify an ordering sequence using the $orderby option.

This means that your $skiptoken values must always end with the ID value of the entry to skip past, as demonstrated in the preceding example which had a $skiptoken value of ‘Task 1899’,1940.

Note: multiple values in a $skiptoken must be separated with a , (comma) value.

To continue the example, using the URL from the __next property returns the next page of results;

Use in the Realworld

So given what we know about server driven paging in the SharePoint REST API, you might say that you’re never going to want to return 1000 entries per page, and you’d be right. In this respect you would obviously limit the number of returned entries per page using the $top query option and then use the values from the last entry returned to formulate the $skiptoken value for the next page of results.

Pagination Controls

An obvious implication of the server driven paging mechanism is quite subtle; that being that while you can know how many pages of data there is to display; this being a function of the desired page size (using $top) and the total number of entries in the candidate result-set (using $inlinecount=allpages), what you can’t know is the skiptoken values required to render links to individual pages of data, the best you can do is offer links to the next and previous page. And while you can mathematically compute “Showing x of y entries” or “Showing page x of y pages” type pagination labels it makes it much more difficult to compute labels any richer than this.

What about the $skiptoken value for a link to the previous page of results? Well you’re going to have to manage that as a piece of state in your application yourself, and as is clear you couldn’t simply use the values from the first entry returned because the starting point of returned entries in a collection is identified as skiptoken+1, so you’d have to save the values of the relevant properties from the first entry returned – 1. Essentially this boils down to saving the skiptoken of the current page of results, which will become the skiptoken of the previous page of results after requesting the next page. And obviously the first page of results will not have a skiptoken at all.

So for use in the realworld, the server driven paging mechanism should offer the fastest possible performance in terms of returning paged access to the requested data, the downside being that managing the next/previous skiptoken values presents a gnarly development problem.

Client Driven Paging

The semantics of client driven paging are completely divorced from the actual data itself, and is more a product of mathematical function involving use of the following query options;

• $top ~ used to control the page size.
• $skip ~ used to set the starting point (as a row number) into the candidate result-set, of the actual page of data served up – this is the only query option we need to vary in order to server up individual pages of data.
• $orderby ~ optional but sets the ordering sequence of the candidate result-set which determines what entries will actually be present in any given page of data.
• $filter ~ optional and restricts the scope or size of the candidate result-set from which pages of data are served up.
• $inlinecount=allpages ~ mandatory, you’ll want this so you can know how many entries there are in the candidate result-set, and not just in the page of data returned.

As mentioned at the top of this article, the SharePoint REST API will not return any more than 1000 entries from a single request.

So ignoring the $filter option for now, lets say that our page size will be 5 and we want to serve up rich pagination controls meaning that we want to be able to create direct links to individual pages, so our URL for page 1 will be;

/sites/team/_vti_bin/listdata.svc/Tasks?$select=Id,Title,Created&$orderby=Created asc&$top=5&$skip=0&$inlinecount=allpages

And the first page of returned data is as shown;

So now lets do some maths, assuming of course that CurrentPageIndex starts at 1;

Total Number of Pages = Math.ceil(data.d.__count / PageSize)

For a direct page link to Page 2, the $skip option will be Math.max(2 – 1, 0) * PageSize

For a direct page link to Page 3, the $skip option will be Math.max(3 – 1, 0) * PageSize

For a link to the next page, the $skip option will be Math.max(Math.min(CurrentPageIndex+1, Total Number of Pages) – 1, 0) * PageSize

For a link to the previous page, the $skip option will be Math.max(Math.max(CurrentPageIndex-1, 1) – 1, 0) * PageSize

The starting row number for the current page is (Math.max(CurrentPageIndex – 1, 0) * PageSize) + 1

The ending row number for the current page is (Math.max(CurrentPageIndex – 1, 0) * PageSize) + PageSize

If you spot any errors in the above logic, then I didn’t write this!

So as we can see, creating rich pagination controls is largely maths, as is managing the single state value needed to maintain the current page index – this is a universe away from the state requirements for server driven pagination.

So whats the downside?

Well, I don’t know for sure, but I’m surmising that there must be a performance hit when using client driven paging.

I’m also assuming that with server driven paging, the listdata.svc engine is able to perform some kind of index seek to the starting entry referred to by the $skiptoken, and from there return only the ($top) number of entries required – so in theory this should be blazingly fast.

In contrast, with client driven paging, listdata.svc must render the full result-set (+/- any $filter option used) skip forward over this result-set the number of entries referred to by the $skip token and then return the ($top) number of entries required.

Since I haven’t performed any detailed analysis of actual performance results I can’t say for sure, and the above is based on supposition, (sparse) documentation and a perusal of the listdata.svc code using Reflector.

Conclusion

After coming across the $skiptoken query option and finding that the sparse documentation for it didn’t seem to mirror SharePoint’s implementation of it (SharePoints use of an implicit ordering sequence using the ID value), groking this little piece of functionality became something of a personal mission.

I have to say now though, that I see very little utility for using it in user interface applications, its use-case I’d say is restricted to that of a fast forward cursor type of access pattern, more likely found in background/back-end processes.

Plug

The user interface tool for REST exploration which you can see in the screen shots can be found and downloaded on codeplex.