What's the best way to get aggregate results from NHibernate?

Question

For example, I am trying to get a min date, a max date, and a sum in different instances. I am trying to avoid hard coding a SQL string or looping through an IList to get these values.

Answer 1

You can run straight queries through NHibernate. Or add additional order by info so the resulting objects are in order via the parameter you're interested in. Order descending and your 0th element is what you want for max and mins. etc etc. There's more than one way to skin the hibernating cat.

Answer 2

If you need queries for reporting, use projections.

Something like:

Session.CreateCriteria ( typeof ( Order ) )
            .Add ( Restrictions.Between ( "DateReceived" , today.AddMonths ( -1 ) , today ) )
            .CreateAlias ( "Lines" , "lines" )
            .SetProjection (
            Projections.ProjectionList ( )
                .Add ( Projections.Sum ( "lines.TotalCost" ) )
                .Add ( Projections.Avg ( "lines.TotalCost" ) )
                .Add ( Projections.GroupProperty ( "Customer" ) )
            ) ;

There's more stuff in Nhibernate Documentation (it's 2/3 of the way down)