I have to create a query similar to the one below to paginate a parent-child query:
SELECT v.ProductId, MAX(v.Price) AS Price
FROM Variant v
GROUP BY v.ProductId
ORDER BY Price ASC;
I am using C#
and NHibernate
. For this purpose, I wrote the following query:
from item in
(from v in session.Query<Variant>()
group v by v.ProductId into g
select new { Id = g.Key, Price = g.Max(x => x.Price) }
)
orderby item.Price
select new { Id = item.Id, Price = item.Price }
But this generates SQL query like below:
SELECT v.ProductId as col_0_0_, MAX(variantrec0_.Price) as col_1_0_
FROM Variant v
GROUP BY v.ProductId
ORDER BY MAX(v.Price) ASC
So I get the following error:
Expressions in the ORDER BY list cannot contain aggregate functions.
Because it does not use my column alias in order-by clause.
How can I create this query using NHibernate IQueryable?
from v in session.Query<Variant>() group v by v.ProductId into g orderby g.Max(x => x.Price) select new { Id = g.Key, Price = g.Max(x => x.Price) }