I wanted to do something that is fairly straight forward to do in plain SQL. I wanted to order a query by one of the grouped columns. Problem was how do you do it in NHibernate.
| 1 | |
| 2 | ICriteria criteria = Persistence.Session.CreateCriteria(typeof(Item)); |
| 3 | criteria.Add(Expression.Gt("Date", monthFrom)); |
| 4 | criteria.SetProjection(Projections.ProjectionList() |
| 5 | .Add(Projections.SqlGroupProjection("year({alias}.ItemDate),month({alias}.ItemDate)", "year({alias}.ItemDate),month({alias}.ItemDate)", new string[] { "year,month" }, new IType[] { NHibernateUtil.Int32 })) |
| 6 | .Add(Projections.Count("ItemID"))); |
| 7 | criteria.AddOrder(Order.Asc("year")); |
| 8 | criteria.AddOrder(Order.Asc("month")); |
| 9 | IList items = criteria.List(); |
HQL
Using IQuery with HQL this works fine
| 1 | select year(i.ItemDate), month(i.ItemDate), count(i.ItemID) |
| 2 | from Item as i |
| 3 | group by year(i.ItemDate), month(i.ItemDate) |
| 4 | order by year(i.ItemDate) desc, month(i.ItemDate) desc |
In the end I gave up tring to get this to work using ICriteria and stuck with IQuery.
This leads to the question what is the better method to use IQuery or ICriteria. My initial thoughs are IQuery is much more flexible however ICriteria provides an elegant method for generating dynamic querys. David Givoni has a related post that comments on a some of performance issues of ICriteria.