NHibernate: returns a strongly typed list using an aggregate function

Question

I want nHibernate to return a strongly typed list from a CreateQuery call using HQL.

We would return a strongly typed list of "MyType", but we would like to apply the aggregate function to the result set before returning it. Unfortunately, as I understand it, adding an aggregated field means that nHibernate cannot match the results with our signature type "MyType".

How do we get around this? The following query describes what we would like to return from the database, but the "feedcount" field affects the ability of nHibernate to determine the list to return.

select feedname, count(feedurl) as feedcount from rsssubscriptions group by feedurl, feedname order by feedcount desc

Presumably, the result set should be generated earlier in the sequence of events to ensure that nHibenate can infer the correct type to instantiate for each record.

thanks

Answer 1

Here is what you need to do:

IList<MyObj> reults = Session.CreateQuery("select r.feedname as feedname, count(r.feedurl) as feedcount from rsssubscriptions r group by r.feedname")
                            .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(myobj)))
                            .List<MyObj>();

And then define the type of MyObj as follows:

public class MyObj 
{
    public virtual long feedcount { get; set; }
    public virtual string feedname { get; set; }
}

Property names of the above type must be the same as the aliases of the returned properties, and all properties must be virtual.

Read the following post for more information on what you can do with the SetResultTransformer () method: http://www.junasoftware.com/blog/nhibernate-setresulttransformer-and-dto.aspx