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.