Getting total result count and allowing pagination in the same query with MongoDB

Question

I have my query set up like so to allow for pagination. While this works, I have to basically run the same query twice to get the total matching results for the query AND allow for pagination. Is there any way to combine this into a single query?

public SearchResult GetResults()
{
    //query is built elsewhere
    var totalResults = (from i in Collection.Find(query)
        select i).Count();

    var results = (from i in Collection.Find(query)
        select i)
        .Skip(recordsToSkip)
        .Take(recordsToTake)
        .ToList();

    //SearchResult is defined elsewhere
    return new SearchResult
    {
        Results = results,
        TotalResults = totalResults
    };
}

Answer 1

First, to get the count you should not do a linq query and then count the results. This way enumerates all the results and then counts them, which is costly. You should instead use:

var totalResults = Collection.Find(query).Count()

This Count method is defined on the MongoCursor itself and will count the results in mongo, instead of in your .Net application.

I guess that was the real problem behind the question. But if you still want to unite the 2 queries you can do that like so:

var results = (from i in Collection.Find(query) select i).ToList();
var totalResults = results.Count();
var page = results
    .Skip(recordsToSkip)
    .Take(recordsToTake)
    .ToList();

That will get the whole collection, count it, and return a page of it. I would not recommend you do that though, because you don't need the whole collection.

P.S: When you use Linq on the result of Find, it does the filtering in your application and not in the DB, so you should change your query to this one:

var results = Collection.Find(query)
    .SetSkip(recordsToSkip)
    .SetLimit(recordsToTake)
    .ToList();