Write a post

Enjoy this post? Give Peter Mbanugo a like if it's helpful.

3
4

Working with MongoDB in .NET (Part 3): Skip, Sort, Limit, & Projections

Published Jan 03, 2017Last updated Jan 18, 2017
Working with MongoDB in .NET (Part 3): Skip, Sort, Limit, & Projections

So far we've looked at creating documents , retrieving document, and now let's look into sorting documents, specifying the number of documents to skip or limit, and how to do projections.

Limit

When we query for a document, we sometimes don't want to return all of the documents that matches our filter criteria, just some of it. And this is where specfying a limit clause comes to use. With MongoDB, you can limit the number of documents by calling the Limit method of IFindFluent returned from calling Find. So if I query my database for students where age is less than 40, I get the following:

S/N: 1 	 Id: 582489339798f091295b9094, FirstName: Gregor, LastName: Felix
S/N: 2 	 Id: 582489339798f091295b9095, FirstName: Machiko, LastName: Elkberg
S/N: 3 	 Id: 582489339798f091295b9096, FirstName: Julie, LastName: Sandal
S/N: 4 	 Id: 583da304f03a84d4d4f4678d, FirstName: Peter, LastName: Cyborg

And to tell it to limit its result to a maximum of two students, I call Limit() with a value of 2:

int count = 1;
await collection.Find(x => x.Age < 40)
    .Limit(2)
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count} \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}");
            count++;
        });

And then we get the following output which returns only two documents:

S/N: 1, 	 Id: 582489339798f091295b9094, FirstName: Gregor, LastName: Felix
S/N: 2, 	 Id: 582489339798f091295b9095, FirstName: Machiko, LastName: Elkberg

Skip

If we want to tell the database how many documents to skip, we use the Skip method from the fluent interface. So it's similar to using the code as we did previously but telling the database to return all with ages less than 40 and skip the first one.

int count = 1;
await collection.Find(x => x.Age < 40)
    .Skip(1)
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count} \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}");
            count++;
        });
S/N: 1, 	 Id: 582489339798f091295b9095, FirstName: Machiko, LastName: Elkberg
S/N: 2, 	 Id: 582489339798f091295b9096, FirstName: Julie, LastName: Sandal
S/N: 3, 	 Id: 583da304f03a84d4d4f4678d, FirstName: Peter, LastName: Cyborg

You'll notice that Gregor Felix was skipped. With skip and sort we can add pagination to our application.

Let's say we want to retrieve every student in the collection with a maximum of two students displayed on a page. We can implement this by:

  • Keeping track of the current page and the max number of documents to retrieve.
  • Determine the total number of pages.
  • Then retrieve the documents while applying skip and limit accordingly.

We can do that by using the following code and print out the result for each page to the console:

var client = new MongoClient();

var db = client.GetDatabase("schoool");

var collection = db.GetCollection<Student>("students");

int currentPage = 1, pageSize = 2;

double totalDocuments = await collection.CountAsync(FilterDefinition<Student>.Empty);
var totalPages = Math.Ceiling(totalDocuments / pageSize);

for (int i = 1; i <= totalPages; i++)
{
    Console.WriteLine($"Page {currentPage}");
    Console.WriteLine();

    int count = 1;
    await collection.Find(FilterDefinition<Student>.Empty)
        .Skip((currentPage - 1) * pageSize)
        .Limit(pageSize)
        .ForEachAsync(
            student =>
            {
                Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}");
                count++;
            });

    Console.WriteLine();
    currentPage++;
}

And we get the following results in the console window:

Page 1

S/N: 1, 	 Id: 58469c732adc9f5370e50c9c, FirstName: Gregor, LastName: Felix
S/N: 2, 	 Id: 58469c732adc9f5370e50c9d, FirstName: Machiko, LastName: Elkberg

Page 2

S/N: 1, 	 Id: 58469c732adc9f5370e50c9e, FirstName: Julie, LastName: Sandal
S/N: 2, 	 Id: 58469c732adc9f5370e50c9f, FirstName: Peter, LastName: Cyborg

Page 3

S/N: 1, 	 Id: 58469c732adc9f5370e50ca0, FirstName: James, LastName: Cyborg

And with that, we get three pages because we have a total of five records and a maximum of two documents retrieved per page.

Sort

The Sort method of the fluent interface takes a SortDefinition, and this is implicitly convertible from strings or BsonDocument much like the FilterDefinition. So if we want to sort by the last name in an ascending order using a string as the sort definition, it will be:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .Sort("{LastName: 1}")
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

Inside the string, we have {LastName: 1} where 1 tells it to sort ascendingly and -1 to sort in a descending order. If we run the app with the previous update, it returns James and Peter as the result on the first page, such as the following:

Page 1

S/N: 1, 	 Id: 58469c732adc9f5370e50ca0, FirstName: James, LastName: Cyborg, Age: 39
S/N: 2, 	 Id: 58469c732adc9f5370e50c9f, FirstName: Peter, LastName: Cyborg, Age: 39

Page 2

S/N: 1, 	 Id: 58469c732adc9f5370e50c9d, FirstName: Machiko, LastName: Elkberg, Age: 23
S/N: 2, 	 Id: 58469c732adc9f5370e50c9c, FirstName: Gregor, LastName: Felix, Age: 23

Page 3

S/N: 1, 	 Id: 58469c732adc9f5370e50c9e, FirstName: Julie, LastName: Sandal, Age: 25

And if we want the LastName to be arranged in a descending order using a BsonDocument, this will be:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .Sort(new BsonDocument("LastName", -1))
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

Giving us the reverse of the previous result:

Page 1

S/N: 1, 	 Id: 58469c732adc9f5370e50c9e, FirstName: Julie, LastName: Sandal, Age: 25
S/N: 2, 	 Id: 58469c732adc9f5370e50c9c, FirstName: Gregor, LastName: Felix, Age: 23

Page 2

S/N: 1, 	 Id: 58469c732adc9f5370e50c9d, FirstName: Machiko, LastName: Elkberg, Age: 23
S/N: 2, 	 Id: 58469c732adc9f5370e50ca0, FirstName: James, LastName: Cyborg, Age: 39

Page 3

S/N: 1, 	 Id: 58469c732adc9f5370e50c9f, FirstName: Peter, LastName: Cyborg, Age: 39

We can also use the SortDefinitionBuilder. So, we can update our code using the builder helper to create a sort definition as follows:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .Sort(Builders<Student>.Sort.Descending("LastName"))
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

We still get back the same result, and we can also combine lists that are on ascending and descending orders on different fields:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .Sort(Builders<Student>.Sort.Descending("LastName").Ascending("FirstName"))
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

Or with a strongly-typed object, use expression trees:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .Sort(Builders<Student>.Sort.Descending(x => x.LastName).Ascending(x => x.FirstName))
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

We can also use expression trees to specify sorting on SortBy, SortByDescending, ThenBy and ThenByDescending methods of the fluent interface. Following our previous example, this will be defined as:

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .SortByDescending(x => x.LastName)
    .ThenBy(x => x.Age)
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

Most of the time, we'll be using a strongly-typed object as it's a lot easier to build queries using expression trees.

Projection

We can also do projections using the Project method of the fluent interface. We specify a projection similar to the way we do for sort and filter.

Using either an expression tree or projection definition results to slightly different behaviors. One of the differences is that, when using the projection definition syntax, you have to explicitly tell it not to exclude the _id field, otherwise, it returns it as part of the result set. Let's update the code to return just the FirstName

await collection.Find(FilterDefinition<Student>.Empty)
    .Skip((currentPage - 1) * pageSize)
    .Limit(pageSize)
    .SortByDescending(x => x.LastName)
    .ThenBy(x => x.Age)
    .Project("{FirstName: 1}")
    .ForEachAsync(
        student =>
        {
            Debug.WriteLine($"S/N: {count}, \t Id: {student.Id}, FirstName: {student.FirstName}, LastName: {student.LastName}, Age: {student.Age}");
            count++;
        });

With the updated code, our application fails to compile. This brings us to another difference: with a projection definition, it implicitly converts the document type from Student to BsonDocument, so what we get back is a fluent object that, in result, will be a BsonDocument (even though what we're working with is the Student type). If we want to work with Student, we have to indicate that we still want to keep the type to Student.

.Project<Student>("{FirstName: 1}")

Therefore, updating our code by setting Student as the type for the method gives the following output:

Page 1

S/N: 1, 	 Id: 58469c732adc9f5370e50c9e, FirstName: Julie, LastName: , Age: 0
S/N: 2, 	 Id: 58469c732adc9f5370e50c9c, FirstName: Gregor, LastName: , Age: 0

Page 2

S/N: 1, 	 Id: 58469c732adc9f5370e50c9d, FirstName: Machiko, LastName: , Age: 0
S/N: 2, 	 Id: 58469c732adc9f5370e50ca0, FirstName: James, LastName: , Age: 0

Page 3

S/N: 1, 	 Id: 58469c732adc9f5370e50c9f, FirstName: Peter, LastName: , Age: 0

You can see that while we only wanted the FirstName, the FirstName and Id was returned while the others maintained default values. To fix this, we explicitly tell it to exclude the Id field with the following update to the projection definition:

.Project<Student>("{FirstName: 1, _id: 0}")

And then running it, we get the desired result with only FirstName returned while the others maintained deault values:

Page 1

S/N: 1, 	 Id: 000000000000000000000000, FirstName: Julie, LastName: , Age: 0
S/N: 2, 	 Id: 000000000000000000000000, FirstName: Gregor, LastName: , Age: 0

Page 2

S/N: 1, 	 Id: 000000000000000000000000, FirstName: Machiko, LastName: , Age: 0
S/N: 2, 	 Id: 000000000000000000000000, FirstName: James, LastName: , Age: 0

Page 3

S/N: 1, 	 Id: 000000000000000000000000, FirstName: Peter, LastName: , Age: 0

We can also use the projection builder .Project<Student>(Builders<Student>.Projection.Include(x => x.FirstName).Exclude(x => x.Id)) which is similar to using definition builder for sort and filter. We can also do a projection using an expression tree then project it to a different result. The following code will return just the first and last name and map it to an anonymous type:

int count = 1;
await collection.Find(FilterDefinition<Student>.Empty)
    .Project(x => new {x.FirstName, x.LastName})
    .ForEachAsync(
        student =>
        {
            Console.WriteLine($"{count}. \t FirstName: {student.FirstName} - LastName {student.LastName}");
            count++;
        });

Console.WriteLine();
1. 	 FirstName: Gregor - LastName Felix
2. 	 FirstName: Machiko - LastName Elkberg
3. 	 FirstName: Julie - LastName Sandal
4. 	 FirstName: Peter - LastName Cyborg
5. 	 FirstName: James - LastName Cyborg

You may have noticed that we didn't explicitly indicate that we want to exclude Id but did just so unlike the other way, and that's because with a strongly typed expression tree it agrees to return only those fields you specified and exclude the others.

Coming up next

Next up in the series we'll look at update and delete operation, and a few tips/points to note and that'll mark the end of this series.

Don't forget to check out the previous tutorials in this series:

Discover and read more posts from Peter Mbanugo
get started
Enjoy this post?

Leave a like and comment for Peter

3
4
4Replies
Mehmet Doğan
a month ago

It is great tutorial for beginners, waiting for upcoming tutorials

Mark Hardwick
2 months ago

Correct me if I’m wrong, but I believe skip has issues if new records are added in between fetching pages. I.e. There are 10 records and we read a page of 5, then add 5 more records, and then read the next page of 5. In this case we’ll get the same records again since Skip counts from the beginning each time.

Peter Mbanugo
2 months ago

Skip counts from the beginning. The value that gets evaluated and passed into Skip tells is how many records to skip (currentPage - 1) * pageSize and then a limit the records to return Limit(pageSize)

danielwertheim
7 months ago

Could be worth mentioning the behaviour of Skip when it comes to large collection/indexes that it then might become IO-bound as mentioned here: https://docs.mongodb.com/ma…

Show more replies

Subscribe to our weekly newsletter