Search code examples
nhibernatehqlcriteria

NHibernate query for object with child collection property

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.

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?

Solution

  • 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