title: NHibernate TOP N Rows Per Group
body: <p>Here is my domain:</p>
  
  <pre><code>public class ForumTheme
  {
      public virtual String Name { get;set; }
  }
  
  public class ForumTopic
  {
      public virtual IList&lt;ForumTheme&gt; Themes { get;set; }
  }
  
  public class ForumMessage
  {
      public virtual IList&lt;ForumTopic&gt; Topics { get;set; }
  
      public virtual DateTime DatePosted { get; set; }
  }
  </code></pre>
  
  <p>What i want to get is:</p>
  
  <p>The list of ForumThemes with top 5 of most recent ForumMessages for each ForumTheme</p>
  
  <p>Is it possible to do that in NHibernate ?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is another possible scenario.</p>
  
  <p><strong>The domain</strong></p>
  
  <p>The domain describes a typical hierarchical blog/news site.</p>
  
  <pre><code>public class Channel
  {
      public virtual Int32 Id { get; set; }
      public virtual String Name { get; set; }
  
      public virtual IList&lt;Category&gt; Categories { get; set; }
  }
  
  public class Category
  {
      public virtual IList&lt;Article&gt; Articles { get; set; }
  }
  
  public class Article
  {
      public virtual String Name { get; set; }
      public virtual DateTime PublishDate { get; set; }
      public virtual Boolean IsActive { get; set; }
  }
  </code></pre>
  
  <p>So one Channel has many Categories in it and one Category has many Articles.</p>
  
  <p>What i want to do here is to grab TOP N Articles for each Channel (ignoring the fact that they come from different categories). The articles will be shown on the landing page of my portal.</p>
  
  <p>In the application i use ViewModels which hold only subset of actual database data.
  Here is my composite ViewModel</p>
  
  <p><strong>The DTOs</strong></p>
  
  <pre><code>public class ChannelDto
  {
      public Int32 Id { get; set; }
  
      public String Name { get; set; }
  
      public List&lt;ArticleDto&gt; Articles { get; set; }
  
      public class ArticleDto
      {
          public String Name { get; set; }
      }
  }
  </code></pre>
  
  <p>The query to get the channels is quite straightforward (we show only those channels which have active articles in it):</p>
  
  <pre><code>Category categoryAlias = null;
  Article articleAlias = null;
  ChannelDto.ArticleDto articleDtoAlias = null;
  
  List&lt;ChannelDto&gt; channels = _session.QueryOver&lt;Channel&gt;()
                                      .Inner.JoinAlias(x =&gt; x.Categories, () =&gt; categoryAlias)
                                      .Inner.JoinAlias(x =&gt; categoryAlias.Articles, () =&gt; articleAlias)
                                      .Where(x =&gt; articleAlias.IsActive)
                                      .SelectList(list =&gt; list
                                          .Select(x =&gt; x.Id)
                                          .Select(x =&gt; x.Name)
                                      )
                                      .List&lt;Object[]&gt;
                                      .Select(x =&gt; new ChannelDto
                                      {
                                          Id = (Int32) x[0],
                                          Name = (String) x[1]
                                      })
                                      .ToList();
  </code></pre>
  
  <p>The query above gives me the list of Channels with active articles. Now i will get the articles themselves:</p>
  
  <pre><code>foreach(var channel in channels)
  {
      channel.Articles = _session.QueryOver&lt;Channel&gt;()
                                 .Inner.JoinAlias(x =&gt; x.Categories, () =&gt; categoryAlias)
                                 .Inner.JoinAlias(x =&gt; categoryAlias.Articles, () =&gt; articleAlias)
                                 .Where(x =&gt; x.Id == channel.Id)
                                 .OrderBy(x =&gt; articleAlias.PublishDate).Desc
                                 .SelectList(list =&gt; list
                                     .Select(x =&gt; articleAlias.Name).WithAlias(() =&gt; articleDtoAlias.Name)
                                 )
                                 .Take(5)
                                 .List&lt;Object[]&gt;
                                 .Select(x =&gt; new ChannelDto.ArticleDto
                                 {
                                     Name = (String) x[0]
                                 })
                                 .ToList();
  }
  </code></pre>
  
  <p>But the 2 queries above are subject to SELECT + 1 problem. I feel that using futures i can do it using lesser round trips. I'm not so familiar with NHibernate yet.
  Is it possible to get everything with one roundtrip and hydrate DTOs? Using something like ROW_NUMBER() OVER (PARTITION BY....) with QueryOver? I don't want to use HQL or raw SQL.</p>