title: C#/NHibernate - Get first 10 records ordered by grouped sum
body: <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The closest I got to succeeding with NHibernate, I believe was this:</p>
  
  <pre><code>ICriteria criteria = NHibernateSession
      .CreateCriteria&lt;SaleItem&gt;("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&lt;Product&gt; l = criteria
      .List&lt;Product&gt;() as List&lt;Product&gt;;
  </code></pre>
  
  <p>Which generated the following SQL (quite similar to mine):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Unfortunately, it failed when executing the query. I'm pretty sure it has something to do with me doing <code>.CreateCriteria&lt;SaleItem&gt;</code> and then asking <code>.List&lt;Product&gt;</code>, but I don't know how to do it the other way.</p>
  
  <p>Any help is deeply appreciated.</p>