GROUP BY and HAVING clauses in nHibernate QueryOver

Question

I am trying to write this particular sql query in nHibernate QueryOver, which I am not very familiar with:

SELECT MessageThreadId FROM MessageThreadAccesses WHERE ProfileId IN (arr) GROUP BY MessageThreadId HAVING COUNT(MessageThreadId) = arr.Count 

where arr is an array of integers (user IDs) that I pass as an argument, and the MessageThreadAccess object looks like this:

 public virtual MessageThread MessageThread { get; set; } public virtual Profile Profile { get; set; } .... 

After reading several threads and experiments, I got this far with my request (trying to get a MessageThread object - it should always be only one or none), but it still doesn't work, and I'm not quite sure what else to try. A request always returns a MessageThreadAccess object, but when reading its MessageThread property, it is always NULL.

 var access = Session.QueryOver<MessageThreadAccess>() .WhereRestrictionOn(x => x.Profile).IsIn(participants.ToArray()) .Select(Projections.ProjectionList() .Add(Projections.Group<MessageThreadAccess>(x => x.MessageThread)) ) .Where( Restrictions.Eq(Projections.Count<MessageThreadAccess>(x => x.MessageThread.Id), participants.Count) ) .TransformUsing(Transformers.AliasToBean<MessageThreadAccess>()) .SingleOrDefault(); return Session.QueryOver<MessageThread>() .Where(x => x.Id == access.MessageThread.Id) .SingleOrDefault(); 

Can someone point me in the right direction or explain what I'm doing wrong?

Thanks in advance.

Answer 1

I think you can try using DTO to store the result instead of trying to match the result with MessageThreadAccess when it is not alone (without a profile).

Perhaps you can try:

 public class MessageThreadCountDTO { public MessageThread Thread { get; set; } public int Nb { get; set; } } 

then

 var profiles = new int[] { 1,2,3,4 }; MessageThreadCountDTO mtcDto = null; var myResult = _laSession.QueryOver<MessageThreadAccess>() .WhereRestrictionOn(x => x.Profile.Id).IsIn(profiles) .SelectList(list => list.SelectGroup(x => x.MessageThread).WithAlias(() => mtcDto.Thread). SelectCount(x => x.MessageThread).WithAlias(() => mtcDto.Nb) ) .Where(Restrictions.Eq(Projections.Count<MessageThreadAccess>(x => x.MessageThread), profiles.Count())) .TransformUsing(Transformers.AliasToBean<MessageThreadCountDTO>()) .List<MessageThreadCountDTO>().FirstOrDefault(); 

will be profile profiles [], not int [], and then the following line:

 .WhereRestrictionOn(x => x.Profile.Id).IsIn(profiles) 

it should be:

 .WhereRestrictionOn(x => x.Profile).IsIn(profiles) 

Hope this helps