HNibernate: help to avoid N+1 query

Question

I've got following query

DetachedCriteria criteria = DetachedCriteria.For(typeof(Income))
                .CreateAlias("Product", "p")
                .SetProjection(
                    Projections.ProjectionList()
                        .Add(Projections.GroupProperty("Product"))
                        .Add(Projections.Sum("Quantity"))
                 );

which is translated to sql:

SELECT   this_.Product_id    as y0_,
         sum(this_.Quantity) as y1_
FROM     income this_
         inner join products p1_
           on this_.Product_id = p1_.Id
GROUP BY this_.Product_id

my domain:

class Product
{
    IList<Income> Incomes {get;set;}
}

class Income
{
    Product Product {get;set;}
}

when iterating returned collection I have one query for each Product entity. How can I grab all collection in one query?

Answer 1

I prefer HQL for that kind of query...

select i.Product, sum(i.Quantity)
from Income i
group by /*all product properties*/

Answer 2

Did you use Enumerable instead of List to execute the query?