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:

• Part 1 — Working with MongoDB in .NET: Driver Basics & Inserting Documents
• Part 2 — Working with MongoDB in .NET: Retrieving Documents with Filter Clause