NHibernate query for object with child collection property

2
votes

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?

2
nhibernatehqlcriteria
it's may be not easy with NHibernate only. I do a mix of NH + Linq, get the employee with account with NH and the calculation with Linq. It's my method, may be not the best but for me it's ok :) - Kris-I

2 Answers

2
votes

As shown here: 

ICriteria.CreateCriteria(typeof(Customer))
.Add(Expression.Eq("Firstname", "Steve"))
.CreateCriteria("Orders")
.Add(Expression.Gt("OrderDate", new  Datetime.Now)))
.List<Customer>();
0
votes

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