How do I run an HqlBasedQuery that returns an unmapped list of objects using nHibernate?

Question

I want to run a query against two tables (which happen to be mapped in ActiveRecord). The query returns a result list that cannot be mapped to an ActiveRecord object (as it is custom aggregate information).

For instance

Dim query_str as string = "Select distinct d.ID, (select count(1) as exp from Sales_Leads where date_created <= :todays_date) as NbrLeads from Dealer d"

Dim q As Queries.HqlBasedQuery = New Queries.HqlBasedQuery(GetType(ICollection), query_str) q.SetParameter("todays_date", DateTime.Today) Dim i As ICollection = ActiveRecordMediator.ExecuteQuery(q)

What I'm looking for is simple execution of SQL, without an ActiveRecord object returned.

So, ideally, I'd be able to look at i("NbrResults") for each item in the collection.

The error I am getting is:

You have accessed an ActiveRecord class that wasn't properly initialized. The only explanation is that the call to ActiveRecordStarter.Initialize() didn't include System.Collections.ICollection class

Answer 1

Here was my final solution:

Dim query_str As String = "SELECT DISTINCT d.ID, count( l ) from LEAD as l join l.Dealer as d where l.DateCreated >= DATEADD(day, -30, :todays_date) GROUP BY d.ID"

Then obtain the active record session (or NHibernate, still don't even know what is returned here):

Dim sess As ISession = activerecordmediator.GetSessionFactoryHolder().CreateSession(GetType(ActiveRecordBase))

Dim q As NHibernate.IQuery = sess.CreateQuery(query_str)

q.SetParameter("todays_date", DateTime.Today) Dim i As IList = q.List() ' get results

In the .aspx page, the result can be accessed in a GridView like so: <%#Container.DataItem(1).ToString()%>

Answer 2

You're stepping outside the NHibernate paradigm to call down to SQL, which is somewhat against the spirit of ORM. It's not 'bad' per-se, but I'd certainly avoid breaking the abstraction if I could to try to maintain a looser coupling.

You can do what you want with a straight HQL query, which will return a collection of tuples containing the results of your query. I explained how to do this here

http://stackoverflow.com/questions/308203/custom-query-with-castle-activerecord/318100#318100

which you might want to have a look at. Even though you must specify a type when you new an HQLBasedQuery, NH is smart enough to know that if you don't select a type instance it should assemble a result-set based on object tuples.

(IMNSHO it's still a bit non-pure - I'd be tempted to try to model this relationship as an object and map it accordingly, but then I'd have to bash the DB into shape to suit the object model and that's not going to fly in all cases.)

Answer 3

Well, this was asked a long time ago but I have a working answer.

public static IList<T> ExecuteQuery<T>(HqlBasedQuery query)
    where T : new()
{
    query.SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(T)));
        var results = (ArrayList)ActiveRecordMediator.ExecuteQuery(query);

    List<T> list = new List<T>(results.Count);
    for (int i = 0; i < results.Count; i++)
    {
        list.Add((T)results[i]);
    }

    return list;
}

This will give you back results of type T. Type T can be anything you want. Type T needs a no argument constructor and it needs public fields or properties that match the column names or aliases in the query you build.

We do this all the time. Particularly when you want to use aggregate function in HQL to produce aggregate data.

A companion function will allow you to just pass in your query as a string as well as any positional parameters you might have:

public static IList<T> ExecuteQuery<T, U>(string hqlQuery, params object[] parameters)
        where T : new()
    {
        return ExecuteQuery<T>(new HqlBasedQuery(typeof(U), hqlQuery, parameters));
    }

Type U is any type that is a valid ActiveRecord type. It doesn't even have to be one of the types you are referencing. If you want you could replace it will some type you know is gonna be valid int he session and ditch the extra parameter.