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.
精彩评论