Amount in QueryOver for NHibernate as part of JoinQueryOver

Question

Although I read the NHibernate cookbook and all the available forum posts up and down, I still cannot fulfill this simple request:

I have users who have one account. Each account has a balance. The classes look like this:

public class User { public virtual int Id { get; set; } public virtual Account Account { get; set; } public virtual bool Active { get; set; } } public class Account { public virtual int Id { get; set; } public virtual double Balance { get; set; } } 

Now I would like to summarize the balance of all active users. Nothing more ... In plain SQL, it's pretty simple:

 SELECT SUM(a.Balance) FROM User u INNER JOIN Account a ON u.Account_id = a.Id WHERE u.Active = 'true' 

I have no idea how I could solve this problem with the new QueryOver-Api from NHibernate 3. Could you give some sample code?

Thank you in advance!

Daniel Lang

EDIT
I know that with NHibernate Linq is also very easy, but I would like to solve it with QueryOver ... Here is a working Linq-Example:

 var result = Session.Query<User>() .Where(x => x.Active) .Sum(x => x.Account.Balance) 

DECISION
Thanks to AlexCuse, I was able to find the final solution (it was very close) - here is the complete code:

 User userAlias = null; Account accountAlias = null; session.QueryOver<User>(() => userAlias) .JoinAlias(() => userAlias.Account, () => accountAlias) .Where(() => userAlias.Active) .Select(Projections.Sum<Account>(acct => accountAlias.Balance)) .SingleOrDefault<double>() 

Answer 1

Have you tried something like this?

 session.QueryOver<User>(() => userAlias) .JoinAlias(() => userAlias.Account, () => accountAlias) .Where(() => userAlias.Active) .Select(Projections.Sum<Account>(acct => acct.Balance)) .UnderlyingCriteria.UniqueResult() 

I'm not sure what the UniqueResult equivalent is in the QueryOver API, so I had to pass the basic criteria.