Paging MongoDB query with C# drivers

Question

I am using version 2.2 of MongoDB drivers for C#. I want to paginate a query : the response to the query must contain the items of the current page and the total count of items matching the query.

I want to do one query. With the mongo shell I can realize that as it :

var c = db.mycol.find({....}).skip(0).limit(10)
var total = c.count();
while (c.hasNext()) {
   print(tojson(c.next()));
}

But with the C# driver, I don't know how to do it with only one query.

var find = collection
  .Find(x => x.Valid == true)
  .Skip(0)
  .Limit(10);

var cursor = await find.ToCursorAsync(cancellationToken);
// How to get the count? There is no method in the IAsyncCursor interface.

Is it possible ? Some ideas ?

Ligni

What do you expect count to return? 10? – Trierarch

No not 10 which is my page size. Imagine my collection contains 10000 records. My query matches to 2000 records. So the total should be 2000. I use the function to iterate through the pages (the value passed to skip function should be parametrized). – Ligni

Answer 1

You can't accomplish your task by sending to DB only one query. The common practice is following

var query = GetCollection().Find(x => x.Valid == true);
var totalTask = query.CountAsync();
var itemsTask = query.Skip(0).Limit(10).ToListAsync();
await Task.WhenAll(totalTask, itemsTask);
return new Page{ Total = totalTask.Result, Items = itemsTask.Result};

Tullis

I came to the same conclusion as you and wrote the exact same code. I was wondering if it is a limitation of the C# drivers as the shell allow to use the same cursor to get total and paged items. Do you think that it could be a improvement to submit to MongoDB team ? – Ligni

In fact they are two different queries. And it is neither driver's issue nor MongoDB's issue. MongoDB team can add a wrapper for your task for convenient using, although you could do it by yourself. – Tullis

Ok. This therefore means that the shell version does the same two requests that the C# version ? In this case, the C# code with two tasks is good and can not be more optimized ? – Ligni

Your shell version has mistake, because it does 'count' after 'limit'. And yes, shell'll do also two queries. – Tullis

Ok if shell does two queries. Then C# version is also good. However shell version has no mistake, because by default count() ignore skip() and limit(). See docs.mongodb.org/v3.0/reference/method/cursor.count – Ligni

it's nice feature! C# driver doesn't have this applySkipLimit option :( – Tullis

Answer 2

Within MongoDB we have the ability to create a data processing pipeline that will get executed against our data once.

public class Person
{
    [BsonId]
    [BsonRepresentation(BsonType.String)]
    public string Id { get; set; }

    public string FirstName { get; set; }

    public string Surname { get; set; }
}

public class Peger
{
    public int Count { get; set; }

    public int Page { get; set; }

    public int Size { get; set; }

    public IEnumerable<Person> Items { get; set; }
}

class Program
{
    static async Task Main(string[] args)
    {
        var client = new MongoClient();
        var database = client.GetDatabase("pager_test");
        var collection = database.GetCollection<Person>(nameof(Person));

        int page = 1;
        int pageSize = 5;
        var results = await GetPagerResultAsync(page, pageSize, collection);
    }

    private static async Task<Peger> GetPagerResultAsync(int page, int pageSize, IMongoCollection<Person> collection)
    {
        // count facet, aggregation stage of count
        var countFacet = AggregateFacet.Create("countFacet",
            PipelineDefinition<Person, AggregateCountResult>.Create(new[]
            {
                PipelineStageDefinitionBuilder.Count<Person>()
            }));

        // data facet, we’ll use this to sort the data and do the skip and limiting of the results for the paging.
        var dataFacet = AggregateFacet.Create("dataFacet",
            PipelineDefinition<Person, Person>.Create(new[]
            {
                PipelineStageDefinitionBuilder.Sort(Builders<Person>.Sort.Ascending(x => x.Surname)),
                PipelineStageDefinitionBuilder.Skip<Person>((page - 1) * pageSize),
                PipelineStageDefinitionBuilder.Limit<Person>(pageSize),
            }));

        var filter = Builders<Person>.Filter.Empty;
        var aggregation = await collection.Aggregate()
            .Match(filter)
            .Facet(countFacet, dataFacet)
            .ToListAsync();

        var count = aggregation.First()
            .Facets.First(x => x.Name == "countFacet")
            .Output<AggregateCountResult>()
            ?.FirstOrDefault()
            ?.Count ?? 0;

        var data = aggregation.First()
            .Facets.First(x => x.Name == "dataFacet")
            .Output<Person>();

        return new Pager
        {
            Count = (int)count / pageSize,
            Size = pageSize,
            Page = page,
            Items = data
        };
    }
}

Lamphere

How we can utilize Atlas Search in this piece of code? – Zelmazelten