Pagination

Skip - Limit

Skip & Limit - $skip & $limit

Skip and Limit operators are operators commonly used for pagination purposes. In most cases they are used together along with a $sort operator.

Operator

Description

Skip

Skips a specified number of documents and pass the result to the next stage

Limit

Limits the number of documents passed to the next state

The sample creates a pagination result on User documents by skipping 3 documents and returning 3 documents at a time. The results are sorted by DateOfBirth.

Limit_skip.cs
var usersCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection);

var topLevelProjection = Builders<User>.Projection
    .Exclude(u => u.Id)
    .Include(u => u.UserName)
    .Include(u => u.DateOfBirth);

var topLevelProjectionResults = await usersCollection
    .Find(Builders<User>.Filter.Empty)
    .Project(topLevelProjection)
    .SortBy(u => u.DateOfBirth)
    .Skip(skipSize)
    .Limit(limitSize)
    .ToListAsync();

To fetch the next page result just change the skip size. The same query can also be built using LINQ as follow:

var usersQueryableCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection)
    .AsQueryable();
    
var linqTopLevelResults = await usersQueryableCollection
    .Select(u => new { u.UserName, u.DateOfBirth })
    .OrderBy(u => u.DateOfBirth)
    .Skip(skipSize)
    .Take(limitSize)
    .ToListAsync();

Paginate array field

Paginating an array field requires at least an extra $unwind stage to deconstruct the array. The sample creates a pagination on the FavoriteSports of the first User document that contains more than 10 items in its favorite sports array field.

Limit_skip.cs
var usersQueryableCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection)
    .AsQueryable();

var user = await usersCollection
    .Find(u => u.FavoriteSports.Count > 10)
    .FirstOrDefaultAsync();

var sliceQuery = usersQueryableCollection
    .Where(u => u.Id == user.Id)
    .SelectMany(u => u.FavoriteSports, (u, s) => new
    {
        id = u.Id,
        sport = s
    })
    .OrderBy(u => u.sport)
    .Skip(skipSize)
    .Take(limitSize)
    .GroupBy(q => q.id)
    .Select(g => new
    {
        id = g.Key,
        sports = g.Select(a => a.sport)
    });

LINQ explanation

  1. Where clause creates a $match stage to match the user document

  2. SelectMany creates an $unwind stage and deconstructs the favoriteSports array. This will produce a new document for each favorite sport in the array

  3. new {} creates a $project stage that passes only the _id and each sport element from the preview stage

  4. OrderBy sorts the documents alphabetically based on the sports

  5. Skip/Take paginate the documents which already sorted by the sports

  6. GroupBy/Select group the documents together in order to create an array of the selected sports containing in the documents retrieved from the previous stage

Last updated