Search code examples
c#mongodbperformancelinq

Why count in mongodb C# LINQ driver implemented via aggregation?

While using C# MongoDB Driver on simple queries we quite often use a following pattern:

var query = Collection.AsQueryable();
query = BuildQuery(query, request)

// Same queryable used to get total and actual values for pagination
total = query.Count();
values = query.Skip(request.Ski[).Take(request.Take);
return { Values = values, Total = total };

Overall everything worked ok till we got a use cases with 100K+ entries matching documents and it started to take 30+ seconds.

It turns out that C# driver converts this request into following aggregation:

db.getCollection('xyzEntity').aggregate([  {
                "$group" : {
                    "_id" : 1,
                    "__result" : {
                        "$sum" : 1
                    }
                }
            }])

which performs much slower than getCollection().find({}).count() which is using index.

According to documentation (*) this is by design but this is very confusing to me. Why it is implemented via aggregation? Probably idea is that I can pass query within Count but how I can change this query to use indexes?

Solution

  • It turns out that this we can help aggregation by sorting things by index: https://stackoverflow.com/a/56427875/11768

    As for linq solution is to change count line in following way:

    var total = query.OrderBy(d => d.Id).Count()