Having in QueryOver

Question

we are struggling with the following problem.. ORM solution of our choice is NHibernate and we want to write queries using QueryOver style.. Right now there is a new puzzle to solve, we want to make a query like:

select sp.Id, SUM(p.PriceAmount), SUM(i.BruttoAmount)  from SellerProfile sp
left join SellerProfile_Invoice spi on spi.SellerProfile = sp.Id
left join Invoice i on spi.Invoice = i.Id
left join SellerProfile_Payment spp on spp.SellerProfile = sp.Id
left join Payment p on spp.Payment = p.Id
where i.PaymentDate < '2011-07-12'
group by sp.Id
having SUM(ISNULL(p.PriceAmount,0)) - SUM(ISNULL(i.BruttoAmount,0)) < 0

So we've wrote code like this:

Invoice invoice = null;
Payment payment = null;
SellerProfile seller = null;

var sellerIds = Session.QueryOver<SellerProfile>(() => seller)
                .Left.JoinQueryOver(() => seller.Payments, () => payment)
                .Left.JoinQueryOver(() => seller.Invoices, () => invoice)
                .Where(() => invoice.PaymentDate < DateTime.Now - timeSpan)
                .Select(Projections.Group(() => seller.Id))
                .Where(Restrictions.Lt(new ArithmeticOperatorProjection("-", NHibernateUtil.Decimal, Projections.Sum(() => payment.Price.Amount), Projections.Sum(() => invoice.Brutto.Amount)), 0)).List<int>();

Generated SQL looks like this:

SELECT this_.Id as y0_ 
FROM SellerProfile this_ inner join ResourceOwner this_1_ on this_.Id=this_1_.Id 
inner join Resource this_2_ on this_.Id=this_2_.Id
left outer join SellerProfile_Payment payments4_ on this_.Id=payments4_.SellerProfile
left outer join Payment payment2_ on payments4_.Payment=payment2_.Id
left outer join SellerProfile_Invoice invoices6_ on this_.Id=invoices6_.SellerProfile
left outer join Invoice invoice1_ on invoices6_.Invoice=invoice1_.Id
WHERE invoice1_.PaymentDate < @p0
and (sum(payment2_.PriceAmount) - sum(invoice1_.BruttoAmount)) < @p1
GROUP BY this_.Id

But it's throwing an exception, because it puts and clause to first where instead of having in the last line and our SQL does not work...

Any help? Thanks...

Answer 1

AFAIK the QueryOver, LINQ, and Criteria API does not support HAVING clause logic (NH 3.1)

You can use HQL though.

HQL Examples