Geek Answers Handbook

NHibernate projects child entities into parent properties with Linq or QueryOver

It may be simple, but I was stuck with this, and I did not find an answer to how this can be done. I have a parent User object with a set of Operations child objects. These two objects are intended only for the user interface, therefore they represent kinfik representations. 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 would I like to do to get a user with TotalSuccesfulAccesses and TotalFailedAccesses initialized from the child collection, in one round trip to the database.

For each user, we must calculate Sum (Operation.NbSuccessfulAccesses) and Sum (Operation.NbFailedAccesse) and make a prediction, respectively, User.TotalSuccesfulAccesses and User.TotalFailedAccesses .

I tried to play with multi criteria and a few queries, but I am not happy with that. I would like to know, maybe there is an easy way to do this with a projection or something else. Or maybe I missed something.

What would you recommend?

Thanks in advance for your help.

+4
source share
3 answers

Thanks 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 way to get rid of the magic string "TotalSuccessfulAccesses" and "TotalFailedAccesses".

if i use something like this

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

NHibernate gives an error:

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

which is incorrect because a setter exists for the TotalSuccessfulAccesses property.

Any ideas?

thanks

+2
source

I managed to get rid of the strings of the magic alias as follows:

 UserViewModel userView = null; Add(Projections.Sum<User>(x => operations.NbSuccessfulAccesses).WithAlias(() => userView.TotalSuccessfulAccesses))
+4
source

You probably need to separate your view models and domain objects. I assume that in your domain you have a User class with the Operation list, and these objects are displayed accordingly.

Then you can 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 a presentation model according to your needs and appropriately add any properties to the forecast list.

Hope this helps.

+3
source

More articles:


All Articles