NHibernate query to fetch top(x) entities for a given List property

Question

I'm having difficulties figuring out how to do the following.

Given the following classes:

public class Post
{
   ...
   public IList<Comment> Comments
   ...    
}

public class Comment
{
    public DateTime CommentDate
    ... Some other properties but no reference to Post...
}

How can I write a query to get only the first 10 comments for a given post ordered by date descending?

As there is no reference from Comment to Post, I can't query on Comment, I need to query on Post, but all my queries seem to return Post, and my attempts at projection have failed.

I can't add a property referencing Post from Comment (BTW that isn't actually my domain model), so I am stuck.

I hope I'm not missing something obvious.

Edit:

This would give me what I want if there was a reference from Comment to Post

var query = (from comment in Session.Query<Comment>() orderby comment.CommentDate 
where comment.Post == some Post select comment).Take(10);

but there is not, so I'm looking for the equivalent query on Post that returns a List of 10 Comments.

If the query is possible via Linq, that's what I'd prefer, but would be happy enough using QueryOver.

I might just end up reworking my Domain Model instead, so that there is that reference.

Answer 1

Here's a solution using HQL:

var postId = 1;
var hql = "select p.Comments from Post p join p.Comments c where p.PostId = :postId order by c.CommentDate desc";
var result = session.CreateQuery(hql)
    .SetParameter("postId", postId)
    .SetMaxResults(10)
    .List<Comment>();

I couldn't figure out a way to do it in the Criteria API.