I have a MongoDB collection, and I need to find the distinct count of records after running a filter. This is what I have right now,
var filter = Builders<Foo>.Filter.Eq("bar", "1");
db.GetCollection<Foo>("FooTable").Distinct<dynamic>("zoo", filter).ToList().Count;
I don't like this solution as it reads the collection in memory to get the count.
Is there a better way to get distinct count directly from db?
The following code will get the job done using the aggregation framework.
var x = db.GetCollection<Foo>("FooTable")
.Aggregate()
.Match(foo => foo.bar == 1)
.Group(foo => foo.zoo,
grouping => new { DoesNotMatter = grouping.Key })
.Count()
.First()
.Count;
The funky "DoesNotMatter" bit seems required (could have a different name) but the driver won't accept null or anything else... Mind you, it gets optimized away anyway and will not be sent to MongoDB.
Also, this code will execute entirely on the server. It won't, however, use indexes so will probably be slower than what you have at this point.
You current code could be shortened into:
db.GetCollection<Foo>("FooTable").Distinct(d => d.zoo, d => d.bar == 1).ToList().Count;
This will use indexes if available but, yes, it will transfer the list of all distinct values back to the client...
