NHibernate aggregate one-to-many query

Question

I have the following objects:

class Topic { public virtual int Id {get; private set;} public virtual ICollection<Vote> Votes {get; private set; } } class Vote { public virtual Topic Topic {get; private set;} public virtual VoteType VotedTo {get; private set;} // enum VotedUp, VotedDown } 

I need to download the following information from db - all topics (identifiers, actually names, but this does not matter in this demo version) and two more fields CountOfVotedUp, CountOfVotedDown (aggregates) . As I understand it in the SQL world, we need joins, grouping by, case and account.

Is it possible to get this information from LINQ with fewer db operations? I mean N + 1, extra allocations, joins, etc.

All I tried was to use NH LINQ, but it aggregates Query only on topic. And I could not count any collection of Votes.

Answer 1

If you have a final class to store your result:

 public class SummaryDTO { public int TopicId { get; set; } public VoteType TypeOfVote { get; set; } public int VoteCount { get; set; } } 

then

 Vote voteAlias = null; SummaryDTO result = null; youNHSession.QueryOver<Topic>() .JoinAlias(x=> x.Votes, ()=>voteAlias) .SelectList( list=>list .SelectGroup(topic=>topic.Id).WithAlias(()=>result.TopicId) .SelectGroup(()=>voteAlias.VotedTo).WithAlias(()=>result.TypeOfVote) .SelectCount(()=>voteAlias.VotedTo).WithAlias(()=>result.VoteCount )) .TransformUsing(Transformers.AliasToBean<SummaryDTO>()) .List<SummaryDTO>(); 

I suppose that is not exactly what you are looking for, but I hope that it will set you up to a good standard.