Order by and Group in NHibernate QueryOver

Question

var data = session.QueryOver<Product>()
                  .JoinAlias(qp => qp.FieldValues, () => qfv)
                  .UnderlyingCriteria.SetProjection(
                      Projections.Group(() => qfv.Product))
                  .AddOrder(Order.Asc("Price"))
                  .List<Product>();

I get error:

"Price cannot appear in OrderBy clause because it is not contained in either and aggregate function or in a group by clause".

As you see I group by "the whole product" and I cannot do what the error says. I cannot group by price (because products will be missing if they have the same price) and I cannot do MIN, MAX (price) for the same reason of eliminating some products. How can I go around it?

Solution 1

Diego is right, group by Product is a group by Product.Id. If you group by Product.Price you'll not miss any products, because you've grouped it by Product.Id too.

You can do this, but don't forget you've got only Product.Id and Product.Price:

var data = session.QueryOver<Product>()
                  .JoinAlias(qp => qp.FieldValues, () => qfv)
                  .SelectList(list => list
                    .SelectGroup(product => product.Id)
                    .SelectGroup(product => product.Price))
                  .OrderBy(product => product.Price).Asc
                  .List<object[]>();

Or you can do this without any group by: (If you want only sort by product.Price)

var data = session.QueryOver<Product>()
                  .JoinAlias(qp => qp.FieldValues, () => qfv)
                  .OrderBy(product => product.Price).Asc
                  .List<Product>();

Solution 2

Maybe this or this will help you understand what's going on behind the scene. But it will still be nice to see the generated SQL code or some nHibernate mapping.

Solution 3

The problem is that this assumption is not correct:

As you see I group by "the whole product"

When you group by Product, what actually happens is that you group by product id.