Pagination in C# against DocumentDB without Skip

Question

I was wondering if there is any way to implement pagination in C# against DocumentDB with, or without, their Linq provider?

Scenario: I have an API which supports pagination, the user sends in the page they want to look at along with a pageSize, such as:

public virtual async Task<HttpResponseMessage> Get(int? page = DefaultPage, int? pageSize = DefaultPageSize)

I then use those parameters to paginate the data in the data access layer with the following code:

return query.Skip((pageNumber - 1) * pageSize).Take(pageSize);

"What is the problem then?", you might ask. Well, this approach and code works perfectly whilst using EF and SQL. The problem is that I want to start using DocumentDB but their Linq-implementation has no support for Skip. The only examples I've seen includes using the TOP keyword or continuation tokens which does not fit well with me allowing users to send in a pageNumber and pageSize.

Is there any implementation that will still allow my users to provide pageNumber and pageSize in the request?

score 12 · Accepted Answer · answered Jan 31 '16 at 22:05

12

SKIP is a performance issue for SQL and it's even worse for NoSQL due to their scale out design. We used MongoDB's SKIP functionality and found that it essentially reran the query from scratch throwing away all of the skipped rows. The later in the list we were skipping to, the longer the query took. So, even though it had SKIP functionality, we were forced to implement a more performant solution.

The product managers at DocumentDB understand this and are resistant to adding SKIP. If they were going to do it, I believe they would have done it when they added TOP.

For DocumentDB, the most efficient approach is to use the continuation token and cache all of the results in order up to (and even predictably beyond) where your user wants. Continuation tokens survive for a long time, so you don't need to fetch all pages immediately.

answered Jan 31 '16 at 22:05

Larry Maccherone

9,393
3
27
43

1

Hello Larry, Thank you for your answer, that sounds like a reasonable argument to make. Although I can't see how using continuation tokens and caching the results will be feasible if my users can send in any `page` and `pageSize` combination. Yes, the highest value of page is equivalent to the number of items in the collection, though wouldn't it be "impossible" to cache this together with all the different combinations yielded by the `page` and `pageSize` combo? Note that `pageSize` can be everything from 1 to around 50. – Joakim Skoog Feb 01 '16 at 07:16
2

Let's say the user has set the page size to 10 and you have 27 rows in the cache. So, the cache only has enough rows for page 1, page 2, and part of page 3 in memory when the user requests page 7. Your code would calculate that it needs to have at least 80 rows in the cache to show page 7 and it will go about fetching that many rows using the continuation token from the last request. If the user then jumps back to page 5, you'll have that in cache already and won't need to hit DocumentDB. In practice you can get 100's or even 1000's of rows in double digit milliseconds. – Larry Maccherone Feb 01 '16 at 17:48
Thank you for the explanation. That sounds like a good strategy, especially as it is dynamic enough to let my users change the `pageSize` between different requests. I guess that you could use the built up cache in requests for single resources as well, which is nice. – Joakim Skoog Feb 01 '16 at 17:54
2

Notice that continuation tokens never expire https://stackoverflow.com/questions/42264464/what-is-the-lifespan-of-continuation-tokens-in-documentdb – Octopus Jan 30 '18 at 09:52

score 6 · Answer 2 · answered Aug 19 '16 at 10:34

Whilst this doesn't answer your question specifically, for future Googlers, Document DB supports paging via continuation tokens. I've written it up in detail here. The code you need it this:

var endpoint = "document db url";  
var primaryKey = "document db key";  
var client = new DocumentClient(new Uri(endpoint), primaryKey);  
var collection = UriFactory.CreateDocumentCollectionUri("database id", "collection id");

var options = new FeedOptions  
{ 
    MaxItemCount = 100 // <- Page size
};

var query = client.CreateDocumentQuery<Document>(collection, options).AsDocumentQuery();

while (query.HasMoreResults)  
{
    var result = await query.ExecuteNextAsync<Document>();

    // Process paged results
}

What happens if I have additional filters sunch as a `.Where()` ? Does the continuationToken automatically persist for the next page? — bit, Jul 07 '18 at 18:53

score 5 · Answer 3 · answered Jul 28 '17 at 18:57

I realize the question already has an accepted (and well said) answer but since this particular SO page is the top result on Google for "DocumentDB skip" I thought I would share my solution here, which is really just an implementation of what Larry has already suggested. I used continuation tokens and caching in Angular to come up with a decent paging mechanism for DocumentDB queries. The key is that I also allow sorting and filtering which reduces the user's need to jump to random pages or even to the last page of results. Here is my solution:

http://www.zoeller.us/blog/2017/7/27/paging-results-with-documentdb

Great solution. I'd really love to see it updated to support showing all of the pages (random jump) in navigation instead of just the next page. — defines, Feb 07 '18 at 16:09

Pagination in C# against DocumentDB without Skip

3 Answers3

Linked