RavenDB version 2.5. Other versions:

Paging

Paging, or pagination, is the process of splitting a dataset into pages, reading one page at a time. This is useful for optimizing bandwidth traffic, optimizing hardware usage, or just because no user can handle huge amounts of data at once anyway.

RavenDB makes it very easy to work with paging. In fact, with large data sets, it makes it mandatory (see: Safe By Default).

It is as simple as specifying a page size and passing a starting point. Using Linq from the Client API, it looks like this:

    // Assuming a page size of 10, this is how will retrieve the 3rd page:
    var results = session.Query<BlogPost>()
    	.Skip(20) // skip 2 pages worth of posts
    	.Take(10) // Take posts in the page size
    	.ToArray(); // execute the query
    
    

Finding the total results count when paging

While paging you sometimes need to know the exact number of results returned from the query. The Client API supports this explicitly:

    RavenQueryStatistics stats;
    var results = session.Query<BlogPost>()
    	.Statistics(out stats)
    	.Where(x => x.Category == "RavenDB")
    	.Take(10)
    	.ToArray();
    var totalResults = stats.TotalResults;
    
    

While the query will return with just 10 results, totalResults will hold the total number of matching documents.

Paging through tampered results

For some queries, RavenDB will skip over some results internally, and by that invalidate the TotalResults value. For example when executing a Distinct query, TotalResults will contain the total count of matching documents found, but will not take into account results that were skipped as a result of the Distinct operator.

Whenever SkippedResults is greater than 0 it implies that we skipped over some results in the index.

In order to do proper paging in those scenarios, you should use the SkippedResults when telling RavenDB how many documents to skip. In other words, for each page the starting point should be .Skip(currentPage * pageSize + SkippedResults).

For example, assuming a page size of 10:

    RavenQueryStatistics stats;
    
    // get the first page
    var results = session.Query<BlogPost>()
    	.Statistics(out stats)
    	.Skip(0 * 10) // retrieve results for the first page
    	.Take(10) // page size is 10
    	.Where(x => x.Category == "RavenDB")
    	.Distinct()
    	.ToArray();
    var totalResults = stats.TotalResults;
    var skippedResults = stats.SkippedResults;
    
    // get the second page
    results = session.Query<BlogPost>()
    	.Statistics(out stats)
    	.Skip((1 * 10) + skippedResults) // retrieve results for the second page, taking into account skipped results
    	.Take(10) // page size is 10
    	.Where(x => x.Category == "RavenDB")
    	.Distinct()
    	.ToArray();
    
    // and so on...
    
    
Comments add new comment

The comments section is for user feedback or community content. If you seek assistance or have any questions, please post them at our support forums.

Ido Ran
REPLY Posted by Ido Ran on

It will be interesting to explain and show example of how to handle pagination while the database is been changed. "Pagination session" may take a long time, several minutes, sometime more. What happen when someone add a new blog post in the middle of the pagination? How is that affect the statistics return on each iteration?

Itamar Syn-Hershko
REPLY Posted by Itamar Syn-Hershko on

If the query you are paging through has new results in it as a result of a recent DB update, you will notice some changes in the order of results which may affect the paging.

Daniel Robinson
REPLY Posted by Daniel Robinson on

Thanks for the examples.

This didn't work for me initially, it was returning the same results over several paging iterations (for a data set that wasn't changing during paging).

To solve the problem I used .OrderBy(...) directly before the .Skip(...) as per Ayende's post on StackOverflow: http://stackoverflow.com/questions/7134963/ravendb-paging-behaviour

Ayende Rahien
REPLY Posted by Ayende Rahien on

If you don't use an OrderBy, the returned results order is undefined, but this shouldn't happen even so. If this is reproducible, please ping us in the mailing list with a failing test.

Bassem Mohsen
REPLY Posted by Bassem Mohsen on

Is it possible to call 'where' before 'skip' and 'take'? Like that we are paging the filtered result set and we will not have to worry about the skipped results.

Ayende Rahien
REPLY Posted by Ayende Rahien on

The order of Where() Skip() and Take() does not matter

John
REPLY Posted by John on

How do i specify OrderBy along with Paging in the above query ?

Ayende Rahien
REPLY Posted by Ayende Rahien on

// Assuming a page size of 10, this is how will retrieve the 3rd page: var results = session.Query<BlogPost>() .Skip(20) // skip 2 pages worth of posts .Take(10) // Take posts in the page size ** .OrderBy(x=>x.PublishDate) ** .ToArray() // execute the query ;

Sean Rock
REPLY Posted by Sean Rock on

Ayende, in your example as OrderBy is specified last, does the OrderBy apply to only page three or does it apply to all BlogPost? Should OrderBy be specified first?

Fitzchak Yitzchaki
REPLY Posted by Fitzchak Yitzchaki on

It doesn't really matter. You can specify OrderBy where that you want. All of the results are ordered, otherwise how can we determine what results should we skip/take?

Eugene
REPLY Posted by Eugene on

Hey It does not seem as if .Skip() command is working i.e

RavenQueryStatistics stats;
        var query = _session.Advanced.LuceneQuery&lt;Study&gt;().Statistics(out stats);
        int grab = 1024;
        int grabbed = 0;
        do
        {
            List&lt;Study&gt; grabCollection = query.Skip(grabbed).ToList();
            Console.WriteLine(grabCollection.Count());
            grabbed += grab; 
        } while (stats.TotalResults &gt; grabbed); // Keep in mind that statsTotalResults will return 2052, however the query will return 1024 each time around through the loop 

Ayende Rahien
REPLY Posted by Ayende Rahien on

You forgot to specify the take, and it will give you a 128 by default without one.

Christian
REPLY Posted by Christian on

When switching the combo from version 2.0 to 2.5 the css code style does not look right, in Google Chrome. What Cms do you use, if I may ask?

Daniel
REPLY Posted by Daniel on

Is it acceptable to use SkipWhile for paging such that you could use a page token to avoid page movement due to additions or removals? E.g.

session.Query<Entity>() .OrderBy(e => e.Title) .SkipWhile(e => e.Title < pageToken) .Take(pageSize);

SUBMIT COMMENT