NHibernate aggregate query for one-to-many relation

Question

I have next entities:

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 load from the db the next info - all topics (IDs, actually Names, but in this demo case it does not matter) and two more fields CountOfVotedUp, CountOfVotedDown (aggregates). So as I understand in SQL world we need joins, group by, case and count.

Is it posible to get this info with LINQ with less operations with db? I mean N+1, additional selects, connections etc.

All that I tried - is to use NH's LINQ, but it's Query aggregates only on Topic.Id and I could not count any of Votes collection.

Coley

Answer 1

Provided you have a summary 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 guess that's not exactly what you are looking for, but hope this will set you on a good track.

Randers

jbl is right here :) Please see my current NHibernate QueryOver example for master-details one-to-many relation to select and aggregate: gist.github.com/s-stude/5528308 – Coley