NHibernate TOP N Rows Per Group

Question

Here is my domain:

public class ForumTheme
{
    public virtual String Name { get;set; }
}

public class ForumTopic
{
    public virtual IList<ForumTheme> Themes { get;set; }
}

public class ForumMessage
{
    public virtual IList<ForumTopic> Topics { get;set; }

    public virtual DateTime DatePosted { get; set; }
}

What i want to get is:

The list of ForumThemes with top 5 of most recent ForumMessages for each ForumTheme

Is it possible to do that in NHibernate ?

UPDATE

Here is another possible scenario.

The domain

The domain describes a typical hierarchical blog/news site.

public class Channel
{
    public virtual Int32 Id { get; set; }
    public virtual String Name { get; set; }

    public virtual IList<Category> Categories { get; set; }
}

public class Category
{
    public virtual IList<Article> Articles { get; set; }
}

public class Article
{
    public virtual String Name { get; set; }
    public virtual DateTime PublishDate { get; set; }
    public virtual Boolean IsActive { get; set; }
}

So one Channel has many Categories in it and one Category has many Articles.

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.

In the application i use ViewModels which hold only subset of actual database data. Here is my composite ViewModel

The DTOs

public class ChannelDto
{
    public Int32 Id { get; set; }

    public String Name { get; set; }

    public List<ArticleDto> Articles { get; set; }

    public class ArticleDto
    {
        public String Name { get; set; }
    }
}

The query to get the channels is quite straightforward (we show only those channels which have active articles in it):

Category categoryAlias = null;
Article articleAlias = null;
ChannelDto.ArticleDto articleDtoAlias = null;

List<ChannelDto> channels = _session.QueryOver<Channel>()
                                    .Inner.JoinAlias(x => x.Categories, () => categoryAlias)
                                    .Inner.JoinAlias(x => categoryAlias.Articles, () => articleAlias)
                                    .Where(x => articleAlias.IsActive)
                                    .SelectList(list => list
                                        .Select(x => x.Id)
                                        .Select(x => x.Name)
                                    )
                                    .List<Object[]>
                                    .Select(x => new ChannelDto
                                    {
                                        Id = (Int32) x[0],
                                        Name = (String) x[1]
                                    })
                                    .ToList();

The query above gives me the list of Channels with active articles. Now i will get the articles themselves:

foreach(var channel in channels)
{
    channel.Articles = _session.QueryOver<Channel>()
                               .Inner.JoinAlias(x => x.Categories, () => categoryAlias)
                               .Inner.JoinAlias(x => categoryAlias.Articles, () => articleAlias)
                               .Where(x => x.Id == channel.Id)
                               .OrderBy(x => articleAlias.PublishDate).Desc
                               .SelectList(list => list
                                   .Select(x => articleAlias.Name).WithAlias(() => articleDtoAlias.Name)
                               )
                               .Take(5)
                               .List<Object[]>
                               .Select(x => new ChannelDto.ArticleDto
                               {
                                   Name = (String) x[0]
                               })
                               .ToList();
}

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.

Answer 1

using 2 rountrips

var themes = session.Query<ForumTheme>().Select(t => new ThemeDto(t.Name)).List();

var topMessages = new List<IEnumerable<string>>(themse.Count);

foreach(var theme in themes)
{
    var query = from message in session.Query<ForumMessage>()
                from topic in message.Topics
                from t in topic.Themes
                where t.Name == theme.Name
                orderby message.DatePosted desc
                select message;
    topMessages.Add(query.Take(5).ToFuture());
}

for(int i = 0;i < topMessages.Count; i++)
{
    themes.TopMessages = topMessages[i].ToList();
}

return themes;

for your second scenario

foreach(var channel in channels)
{
    channel.Articles = _session
        .QueryOver<Channel>()
        .Inner.JoinAlias(x => x.Categories, () => categoryAlias)
        .Inner.JoinAlias(x => categoryAlias.Articles, () => articleAlias)
        .Where(x => x.Id == channel.Id)
        .OrderBy(x => articleAlias.PublishDate).Desc
        .SelectList(list => list
            .Select(x => articleAlias.Name).WithAlias(() => articleDtoAlias.Name)
        )
        .TransformUsing(Transformers.AliasToBean<ChannelDto.ArticleDto>());
}

// execute all subqueries at once through iterating one
channels[0].Articles.Any();