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.

Which version of NH are you using? – kay.herzam Apr 27 '11 at 08:21 — kay.herzam, Apr 27 '11 at 08:21
I'm using 3.1 version of NHibernate – Tomasz Jaskuλa Apr 27 '11 at 08:23 — Tomasz Jaskuλa, Apr 27 '11 at 08:23

score 4 · Answer 1 · answered May 04 '11 at 14:55

4

I was able to get rid of the magic alias strings in the following way:

UserViewModel userView = null;

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

answered May 04 '11 at 14:55

Dyadenka

81
3

kay.herzam · Answer 2 · 2011-04-27T09:41:17.753

You probably need to separate your view models and your domain entities. I assume in your domain you have got a User class having a list of Operation and these entities are mapped accordingly.

You could then create a view model:

public class UserViewModel
{
    public int UserId { get; set; }
    public int TotalSuccessfulAccesses { get; set; }
    public int TotalFailedAccesses {get; set;}
}

Using ICriteria you can create the following query:

var criteria = Session.CreateCriteria(typeof(User));
criteria.CreateAlias("Operations", "operations", JoinType.LeftOuterJoin);

var projList = Projections.ProjectionList();

projList.Add(Projections.GroupProperty("Id"));
projList.Add(Projections.Sum("operations.NbSuccessfulAccesses"), "TotalSuccessfulAccesses"); 
projList.Add(Projections.Sum("operations.NbFailedAccesses"), "TotalFailedAccesses");

criteria.SetProjection(projList);
criteria.SetResultTransformer(Transformers.AliasToBean<UserViewModel>());

var ret = criteria.List<UserViewModel>();

Create the view model according to your needs and add any properties in the projection list accordingly.

Hope that helps.

projList.Add(Projections.Sum("operations.TotalSuccessfulAccesses") shouldn't be projList.Add(Projections.Sum("operations.NbSuccessfulAccesses") — Tomasz Jaskuλa, Apr 27 '11 at 09:37

score 2 · Accepted Answer · answered Apr 27 '11 at 10:48

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

NHibernate projecting child entities into parent properties with Linq or QueryOver

3 Answers3