NHibernate projecting child entities into parent properties with Linq or QueryOver

Question

Maybe it's simple but I'm stuck with it and I didn't find any answer on how it could be done. I have a parent entity User with a collection of child entities Operations. These two entities are just for UI so they are a kinf of views. Here is the pseudo code

public class User
{
   public int Id {get; set;}
   public IEnumerable<Operation> Operations {get; set;}
   public int TotalSuccessfulAccesses {get; set;} // not mapped to the database
   public int TotalFailedAccesses {get; set;}     // not mapped to the database
}

public class Operation
{
   public int Id {get; set; }
   public int UserId {get; set; } // FK
   public int NbSuccessfulAccesses {get; set; }
   public int NbFailedAccesses {get; set; }
}

What I would like to do it's to get the User with TotalSuccesfulAccesses and TotalFailedAccesses initialized from the child collection in one round trip to the database.

For each user we should calculate Sum(Operation.NbSuccessfulAccesses) and Sum(Operation.NbFailedAccesse) and make a projection respectively to the User.TotalSuccesfulAccesses and User.TotalFailedAccesses.

I tried to play with multicriteria and several queries but I'm not satisfied with it. I would like to know if maybe there is a simple way to do it with projection or something other. Or maybe I missed something.

What would you recommend ?

Thanks in advance for you help.

Answer 1

Thanks to Kay, I came up with the following translation :

Operation operations = null;
var q = GetSession().QueryOver<User>().Where(u => u.AccessKeyId == accessKeyId)
                    .Left.JoinQueryOver(x => x.Operations, () => operations)
                    .Select(Projections.ProjectionList()
                        .Add(Projections.Sum<User>(x => operations.NbSuccessfulAccesses), "TotalSuccessfulAccesses"))
                        .Add(Projections.Sum<User>(x => operations.NbFailedAccesses), "TotalFailedAccesses"))
                    .TransformUsing(Transformers.AliasToBean<UserViewModel>()).List< UserViewModel >();

However I would like to know if there is a mean to get rid of the magic string "TotalSuccessfulAccesses" and "TotalFailedAccesses".

if I use something like that

UserViewModel userView = null;

Add(Projections.Sum<User>(x => operations.NbSuccessfulAccesses),  () => userView.TotalSuccessfulAccesses)

NHibernate yields an error :

Could not find a setter for property 'userView.TotalSuccessfulAccesses' in class 'Domain.Query.UserViewModel'

which is not true because there is a setter for TotalSuccessfulAccesses' property.

Any ideas ?

Thanks