NHibernate Aggregate Subquery

Question

Hi all,

I have a problem with NHibernate that I don't seem to be able to find a simple way around.

I have the following database:

Game: ID, Score, Match_ID

Match: ID

A match consists of 3 games.

I want to find out what the maximum Match score is, so the following SQL would do the trick:

select max(a.total) from 
  (select Match.ID, sum(Game.Score) as total 
     from Game inner join Match
     on Game.Match_ID = Match.ID
     group by Match.ID) a

In NHibernate, this appears to be a bit trickier. Apparently, HQL does not allow subqueries in the from clause, so I can't really use this.

I'm pretty sure that it can be done with ICriteria, but I've only just started using NH so I can't seem to figure it out. I've basically got to the following:

Session.CreateCriteria<Game>()
    .SetProjection(Projections.ProjectionList()
     .Add(Projections.GroupProperty("Match"))
     .Add(Projections.Sum("Score"))).List();

After that I've played around with various assortments of DetachedCriteria, but just seem to be going round in circles.

Answer 1

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 = NHibernateSessionManager.Instance.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", NHibernateUtil.Int32); //
sqlQuery1.AddScalar("total", NHibernateUtil.Int32);
sqlQuery1.SetMaxResults(1);    
var result = sqlQuery1.List();

Answer 2

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<long>();

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>("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<IDictionary>()["total"];