2

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?

3
  • GitHub Copilot thinks you can simplify the LINQ to: 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) } Commented Jul 1 at 13:38
  • @DavidOsborne My question is not about how to summarize the query. To be clear about how this query works, I have written it like this and to try to solve the problem. The main problem is that you can put a column alias in the order by clause, but you cannot put the aggregation function. Commented Jul 1 at 13:55
  • Sure, I was thinking more about how reorganising the LINQ would allow the NH LINQ provider to interpret your query correctly. Commented Jul 1 at 13:58

0