Selecting Aggregate Data With Castle.ActiveRecord

Lets say your using Castle's ActiveRecord and you have a complex query to aggregate some data, but you want that query to return the data in a typed class, just not one that NHibernate or ActiveRecord know about...

Here’s what I ended up doing. Creating the SQL query then using the HqlBasedQuery class. There are two important details regarding this, first the query needs to name the columns using the SQL as keyword and setting the Scalar definition. Second, you need to set a transformer using the SetResultTransformer method. Luckily, NHibernate includes a class that will map named scalars to matching properties or fields. More about that here, in section 14.1.5.

Here’s the code:

var sql = @“
select convert(varchar, published, 101) as [Date],
count(state) as [Total],
sum(case when state = 2 then 1 else 0 end) as [Responded]
from notices n
inner join sources s on n.sourceid = s.id and s.userid = :userid
where published >= dateadd(day, -7, getdate())
group by convert(varchar, published, 101)”;





  HqlBasedQuery q = new HqlBasedQuery(typeof (Notice), QueryLanguage.Sql, sql);
  q.AddSqlScalarDefinition(NHibernateUtil.String, “Date”);
  q.AddSqlScalarDefinition(NHibernateUtil.Int32, “Total”);
  q.AddSqlScalarDefinition(NHibernateUtil.Int32, “Responded”);
  q.SetParameter(“userid”, Id);
  q.SetResultTransformer(Transformers.AliasToBean(typeof(NoticeCount)));





  var results = ActiveRecordMediator.ExecuteQuery(q) as ArrayList;
  if (results == null)




<span class="kwrd">return</span> <span class="kwrd">new</span> NoticeCounts();


  var counts = results.ToArray(typeof (NoticeCount)) as NoticeCount[];

  return new NoticeCounts { Counts = counts };