NHibernate query for object with child collection property

Question

Hi,

I need help with an nhibernate query. I would prefer to use Criteria API if possible, otherwise HQL is ok.

I have an Employee object with an Account object property, the Account has a collection of Entry objects, and each Entry has an Amount property. (see class diagram).

http://www.threeboxes.com.au/employeeQuery1.jpg

I need a query that will return all employees who have an account where the sum of the Entry.Amount is less than zero.

Any ideas?

Answer 1

In case it helps... This issue was solved using a Named Query. Not sure whether it is possible with Criteria API.

query:

select employee.* from employee join (select accountid, sum(amount) as balance from entry group by accountid) as accountbalances on accountbalances.accountid = employee.account where accountbalances.balance < 0