QueryOver help on JoinAlias and filter in subqueries

For in开发者_如何学JAVAstance imagine you have an Entity 'Post' that has 'Comments' (one-to-many), and you want to have a view model with the Post entity and the most recent comment:

PostViewModel { Id, Title, Body, Date, LastComment (type:CommentEntity) }

I can do this in plain sql like:

SELECT TOP 10 *
FROM Posts
 INNER JOIN Comments ON Comments.PostID = Posts.PostID
WHERE Comments.[Date] = 
 (SELECT MAX(c.[Date]) FROM Comments AS c WHERE c.PostID = Posts.PostID GROUP BY c.PostID)

How can I do this same query in nhibernate 3 using QueryOver?

I tried with subqueries but I only can get a single result and not the top 10 list.

---

You can try using collection filters to get the most recent Comment for Post:

var posts = session.CreateCriteria<Post>()
    .SetMaxResults(10)
    .List<Post>();

foreach (Post post in posts) {

    Comment lastComment = session.CreateFilter(post.Comments, 
                                               "order by this.Date desc")
        .SetFirstResult(0)
        .SetMaxResults(1)
        .List()
        .FirstOrDefault();

    new PostViewModel  {
        Id = post.Id,
        Title = post.Title,
        LastComment = lastComment
    };
}

---

I've tried to solve your question but I can't try my code at the moment

Comments coms = null;
Post pst = null;

var qOverInclude = QueryOver.Of<Comments>(() => coms)
     .Select(Projections.Max(coms.Date)
      , Projections.Group(()=>coms.PostID));

var qOver = _HibSession.QueryOver<Post>(() => pst)
      .JoinAlias(() => pst.Comments, () => coms, JoinType.LeftOuterJoin)
      .WithSubquery.WhereProperty(() => coms.Date).In(qOverInclude)
      .Take(10)
      .List<Post>();

I hope it's helpful.