I have these tables:

Invoice
Id
Date
Serial
...other fileds

InvoiceDetails
Id
IdInvoice
UnitPrice
Quantity
Total

I want to create NHibernate QueryOver statement to sum Total field for each invoice and then compare with value, for instance i need all invoice that sum of total is equal to 100.00.

In SQL should be like this:

select  i.Id
from Invoice i inner join InvoiceDetails id on i.Id = id.IdInvoice
group by i.Id
having sum(id.Total) = ?--some value 

I have this but it does not work because it sums the total of all Invoice Details, it does not group by Invoice:

var invoices = QueryOver.Of<InvoiceDetails>()
                    .Where(Restrictions.In(Projections.Sum<InvoiceDetails>(item => item.Total)
                        , new List<decimal> { filter.Total/*parameter*/ }))
                    .Select(details => details.IdInvoice.Id)
                    .DetachedCriteria;  

Related posts

Recent Viewed