NHibernate Aggregate Subquery?

For the HQL world a single query does the trick.

For the HQL world a single query does the trick... var maxScore = session. CreateQuery(@"select sum(game. Score) from Game game group by game.

Match order by sum(game. Score) desc") . SetMaxResults(1) .UniqueResult(); Hope this helps.. Update: And for the Criteria world, may be there is a better result transformation but this just works :) var max = (int)session.

CreateCriteria("game") . SetProjection(Projections.ProjectionList() . Add(Projections.

GroupProperty("game. Match")) . Add(Projections.

Sum("game. Score"), "total")) . AddOrder(Order.

Desc("total")) . SetMaxResults(1) . SetResultTransformer(Transformers.

AliasToEntityMap) .UniqueResult()"total".

Thanks, the HQL works a treat. – Carl Nov 5 '09 at 19:40.

I would actually do it this way in SQL: select top 1 Match. ID, sum(Game. Score) as total from Game inner join Match on Game.

Match_ID = Match. ID group by Match. ID order by total desc group by is always tricky in Criteria/HQL: because a group by clause can only return the grouped column and aggregates of any other column.

Therefore it is impossible to return a whole entity from a group by clause, only the ID that you are grouping by and aggregates. For this reason I usually do group by queries using native SQL like this: ISQLQuery sqlQuery1 = NInstance.GetSession(). CreateSQLQuery("select Match.ID, sum(Game.

Score) as total from Game inner join Match on Game. Match_ID = Match.ID group by match. ID order by total desc"); sqlQuery1.

AddScalar("id", NInt32); // sqlQuery1. AddScalar("total", NInt32); sqlQuery1. SetMaxResults(1); var result = sqlQuery1.List().

I didn't even think about changing the query like that, thanks! I'm actually 'one of those' that would have previously done most things as stored procedures, so putting SQL in my code is probably not gonna happen. HQL scared me at first, but I think that it is pretty powerful, so I'll be going down that route I think.

– Carl Nov 5 '09 at 19:39 Yeah! Except the other guy pinched my SQL and made it into HQL! – reach4thelasers Nov 6 '09 at 18:03.