Custom query with Castle ActiveRecord

Question

I'm trying to figure out how to execute a custom query with Castle ActiveRecord.

I was able to run simple query that returns my entity, but what I really need is the query like that below (with custom field set):

select count(1) as cnt, data from workstationevent where serverdatetime >= :minDate and serverdatetime < :maxDate and userId = 1 group by data having count(1) > :threshold

Thanks!

Answer 1

In this case what you want is HqlBasedQuery. Your query will be a projection, so what you'll get back will be an ArrayList of tuples containing the results (the content of each element of the ArrayList will depend on the query, but for more than one value will be object[]).

HqlBasedQuery query = new HqlBasedQuery(typeof(WorkStationEvent),
    "select count(1) as cnt, data from workstationevent where 
     serverdatetime >= :minDate and serverdatetime < :maxDate 
     and userId = 1 group by data having count(1) > :threshold");

var results = 
    (ArrayList)ActiveRecordMediator.ExecuteQuery(query);
foreach(object[] tuple in results)
{
    int count = (int)tuple[0]; // = cnt
    string data = (string)tuple[1]; // = data (assuming this is a string)

    // do something here with these results
}

You can create an anonymous type to hold the results in a more meaningful fashion. For example:

var results = from summary in 
    (ArrayList)ActiveRecordMediator.ExecuteQuery(query)
    select new {
        Count = (int)summary[0], Data = (string)summary[1]
    };

Now results will contain a collection of anonymous types with properties Count and Data. Or indeed you could create your own summary type and populate it out this way too.

ActiveRecord also has the ProjectionQuery which does much the same thing but can only return actual mapped properties rather than aggregates or functions as you can with HQL.

Answer 2

Be aware though, if you're using ActiveRecord 1.0.3 (RC3) as I was, this will result in a runtime InvalidCastException. ActiveRecordMediator.ExecuteQuery returns an ArrayList and not a generic ICollection. So in order to make it work, just change this line:

var results = (ICollection<object[]>) ActiveRecordMediator.ExecuteQuery(query);

to

var results = (ArrayList) ActiveRecordMediator.ExecuteQuery(query);

and it should work.

Also note that using count(1) in your hql statement will make the query return an ArrayList of String instead of an ArrayList of object[] (which is what you get when using count(*).)

Just thought I'd point this out for the sake of having it all documented in one place.