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

<p>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):</p>

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

<p>Thanks!</p>

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.