NHibernate: Group By Case Statement

Today I wanted to speed up some statistics tables which had been tossed into our application dashboard awhile back. The queries were originally written quick and dirty to just pull back all of the records in the table then run some linq counts on them. Terrible of course and there was a nice little comment above the section saying something about "TODO: make this better".

The quickest query I could think of to run these statistics looks something like this:

select 
  IsJourneyman, 
  case when Accepted is null then 1 else 0 end, 
  Count(*)
from 
  students
group by 
  IsJourneyman, 
  case when Accepted is null then 1 else 0 end

My next thought was "Can I implement this in ICriteria?" Well it turns out the answer is Yes!

var cr = Session.CreateCriteria<Student>();

cr.SetProjection(Projections.ProjectionList()
  .Add(Projections.RowCount(), "Count")
  .Add(Projections.Group<Student>(s => s.IsJourneyman), "IsJourneyman")
  .Add(Projections.GroupProperty(
    Projections.SqlProjection("case when Accepted is null then 1 else 0 end",
      new[] { "IsApplicant" }, new[] { NHibernateUtil.Boolean })), 
      "IsApplicant")
);

// transform the results into a strong typed object
cr.SetTransformer(Transformers.AliasToBean(typeof(CountResult)));

return cr.List<CountResult>();

For which I created this simple class. The aliases passed to .Add() are used to match up the properties.

public class CountResult
{
  public bool IsJourneyman { get; set; }
  public bool IsApplicant { get; set; }
  public int Count { get; set; }
}

I tried using the Projections.Conditional, but ran into a NHibernate bug on mixing named and ordered parameters.

I'm sure there are faster ways to accomplish this, but since I wanted to do it in NHibernate without adding a calculated field this will work for me.