Pagination in C# against DocumentDB without Skip

问题:

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?

回答1:

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.

回答2:

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
}

回答3:

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