C#/NHibernate - Get first 10 records ordered by grouped sum

Question

Well, basically I'm trying to figure out a way to get the 10 most sold products. Something I could easily achieve with the following SQL Query:

SELECT `product`.*
FROM `product`
INNER JOIN `sale_item` ON `product`.`id` = `sale_item`.`product_id`
GROUP BY `product`.`id`
ORDER BY SUM(`sale_item`.`quantity`) DESC
LIMIT 10;

The closest I got to succeeding with NHibernate, I believe was this:

ICriteria criteria = NHibernateSession
    .CreateCriteria<SaleItem>("SaleItem")
    .SetMaxResults(10)
    .CreateCriteria("ID.Product")
        .SetProjection(Projections.ProjectionList()
            .Add(Projections.GroupProperty("ID.Product"))
            .Add(Projections.Sum("SaleItem.Quantity"), "QuantitySum")
        )
        .AddOrder(Order.Desc("QuantitySum"));

List<Product> l = criteria
    .List<Product>() as List<Product>;

Which generated the following SQL (quite similar to mine):

SELECT this_.product_id AS y0_,
       sum(this_.quantity) AS y1_
FROM sale_item this_
INNER JOIN product product1_ ON this_.product_id=product1_.id
GROUP BY this_.product_id
ORDER BY y1_ DESC LIMIT 10;

Unfortunately, it failed when executing the query. I'm pretty sure it has something to do with me doing .CreateCriteria<SaleItem> and then asking .List<Product>, but I don't know how to do it the other way.

Any help is deeply appreciated.

score 3 · Accepted Answer · answered Oct 18 '12 at 08:35

You can use Transformers.AliasToBean<Product>() result transformer:

ICriteria criteria = NHibernateSession
    .CreateCriteria<SaleItem>("SaleItem")
    .SetMaxResults(10)
    .CreateCriteria("ID.Product")
        .SetProjection(Projections.ProjectionList()
            .Add(Projections.GroupProperty("ID.Product"), "ID")
            .Add(..., "...") // another Product property
            .Add(Projections.Sum("SaleItem.Quantity"), "QuantitySum")
        )
        .AddOrder(Order.Desc("QuantitySum"));

List<Product> l = criteria
    .SetResultTransformer(Transformers.AliasToBean<Product>());
    .List<Product>() as List<Product>;

score 1 · Answer 2 · answered Oct 18 '12 at 07:39

You are projecting the data, therefore you are not getting Product (nor SaleItem) back from the database. You need to use the non-generic List() to get a list of objects. Those objects will be object arrays, with elements corresponding to the projected values.

To get the entire Product in one go, you have to stick that query in a subquery, where the outer query returns the full product data for the products that are identified by the subquery.

C#/NHibernate - Get first 10 records ordered by grouped sum

2 Answers2

Linked