RavenDB Survival Tip #2: Simulating select count(*)
Categories: RavenDB
A lot of the time a user will ask for a count of some business entity on a report so they can tell how many of something is happening. With SQL, this is a very natural process with aggregate operations:
select count(*) from Users where IsAdmin = true
But with RavenDB, I found it difficult to wrap my mind around how you would do this with an Index. Aggregate operations like this are implemented with map/reduce indexes. Since the reduce step of the index needs to have the same output shape as the map, we can’t simply return a single numeric value as is the case with SELECT COUNT(*) in SQL. We need to perform a LINQ group-by. But in this case, I don’t really want to group by anything in a document, I just want a count.
After a little thinking and digging around, I found a solution. Maybe this is obvious to most, but I found that if we simply group by a constant, we can get a single numeric value for that constant the represents the total count.
Here’s a sample Index definition that demonstrates this concept. In the Index, I am trying to find all User documents that qualify as admins (IsAdmin = true).
public class Users_AdminCounts : AbstractIndexCreationTask<User, Users_AdminCounts.ReduceResult>
{
public class ReduceResult
{
public string Name { get; set; }
public int Count { get; set; }
}
public Users_AdminCounts()
{
// the Name parameter to the reduce function is dummy
// to get it to aggregate to one single value
Map = users =>
from user in users
where user.IsAdmin = true
select new { Name = "Total", Count = 1 };
Reduce = results =>
from result in results
group result by result.Name
into g
select new { Name = g.Key, Count = g.Sum(x => x.Count) };
}
}
When you run the query and get the result (ReduceResult), you will get just one single result and the Count property will contain the count aggregate you are looking for.