Hi I am doing a complex search following the example of Ayende's Blog at this address

http://www.ayende.com/Blog/archive/2006/12/07/7055.aspx

Unfurtunately I couldn't find how to sum the multiplication of 2 columns with Criteria.
My HQL (just an abstract) is

select distinct c
from Customer c
where
(
select sum(d1.UnitPrice*d1.Quantity)
from Customer c1
join c1.Orders o1
join o1.Detail d1
where o1.Customer.Id = c.Id
) > 10000

How can I translate the above HQL it to Criteria?

Thank you

Can anyone help me?

I haven't tried this, but have you checked out the docs on Projections (Section 12.7) and Projections.SqlProjection()? That would seem to be the place to start.

I read it on the reference.....but there is nothing about sum of multiplication.....

....any other suggestion? :)

thank you

Unless I'm mistaken SqlProjection was included in the API so you could compose your own projection from an SQL string in complex situations like these. E.g.,



Not sure how all the subquery correlation works, but that seems to be covered elsewhere.