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.

Categories: RavenDB

Tags: RavenDB, SQL