Expressions in the ORDER BY list cannot contain aggregate functions.

[mehranrezaei]

I create a simple query like below:

var query = session.Query<VariantRecord>()
    .GroupBy(x => x.ProductId)
    .Select(g => new { ProductId = g.Key, MinPrice = g.Min(p => p.Price) })
    .OrderBy(o => o.MinPrice)
    .ToList();

I get error Expressions in the ORDER BY list cannot contain aggregate functions. The generates SQL looks like below:

select
        variantrec0_.ProductId  as col_0_0_,
        min(variantrec0_.Price)  as col_1_0_
from
        VariantRecord variantrec0_
group by
        variantrec0_.ProductId  
order by
        min(variantrec0_.Price) asc

I get error because it does not use the alias (col_1_0_) in order-by clause.

What is the solution?

[hazzik]

This depends on your DB. Solution would likely be to order in memory.

[mehranrezaei]

My table contains millions of records, it is not possible to sort in memory.

As I mentioned above, a query like below works correctly. I don't think it depends on the database. The problem is that NHibernate does not pay attention to the defined alias.

select
        variantrec0_.ProductId  as col_0_0_,
        min(variantrec0_.Price)  as col_1_0_
from
        VariantRecord variantrec0_
group by
        variantrec0_.ProductId  
order by
        col_1_0_ asc

[hazzik]

You're welcome to submit a PR with fixes. The problem is with your RDBMS dialect that does not support this: other RDBMS do support the SQL generated by NHibernate in this case.

[mehranrezaei]

One goal of using an ORM is coding uniformity for different databases. I use SQL CE.