NHibernate QueryOver Sum within JoinQueryOver

Question

Though I was reading through the NHibernate Cookbook and all available forum-posts up and down, I'm still not able to get this simple query done:

I have users with everyone having one account. Each account hast a balance. The classes look like that:

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 sum the balance of all active users. Nothing more... In plain SQL it is quite easy:

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

I don't have any I idea, how I could solve that with the new QueryOver-Api from NHibernate 3. Could you please provide a code-example?

Thank you in advance!

Daniel Lang

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

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

SOLUTION
Thanks to AlexCuse I could find the final solution (he was very very close) - here is the full 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>()

Stupefy

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 had to go through the underlying criteria.

Bifoliolate

Thank you very much! You had just typo there - use "accountAlias.Balance" instead of "acct.Balance". See my edit above. Anywhere aliases did the trick! – Stupefy

Ah, that SingleOrDefault is what I was looking for. Hopefully I can remember it. Surprised acct.Balance wouldn't work but maybe the alias trumps whatever is passed into the projection. – Bifoliolate

Answer 2

You wrote in your answer:

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>()

You don't need to have an alias for the generic type. It could be:

Account accountAlias = null;

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

Peanuts