mysql - 如何在NHibernate QueryOver中的Join语句中而不是在Where block 中指定多个条件

Question

以下 QueryOver 在Where block 中生成子查询，但我宁愿寻找在连接语句上指定该条件的方法

var productsWithLatestComments = _sessionHelper.GetSession().QueryOver(() => p)
                .Left.JoinAlias(() => p.Comments, () => cm)
                .WithSubquery.Where(() => cm.CommentDate == QueryOver.Of<Comment>()
                    .Where(c => c.Product.Id == p.Id)
                    .SelectList(list => list.SelectMax(c => c.CommentDate)).As<DateTime>())
                .Where(() => p.Status != "NOT SOLD" )
                .SelectList(list => list ....GET THE LIST OF COLS.........

这会生成类似的内容

SELECT this_.id as y0_, ......... FROM product this_ 
left outer join comment cn1_ on this_.id=cn1_.product_id 
WHERE cn1_.comment_date = (SELECT max(this_0_.created_date) as y0_ FROM comment this_0_ WHERE this_0_.product_id = this_.id) and (not (this_.status = ?p0);?p0 = 'Sold Out'' [Type: String (18)]

但我一直在寻找

SELECT this_.id as y0_, ......... FROM product this_ 
left outer join comment cn1_ on this_.id=cn1_.product_id and cn1_.comment_date = (SELECT max(this_0_.created_date) as y0_ FROM comment this_0_ WHERE this_0_.product_id = this_.id)
WHERE (not (this_.status = ?p0);?p0 = 'Sold Out'' [Type: String (18)]

Answer 1

好吧，我通过在连接别名中指定子查询而不是在 .WithSubquery 中指定子查询，在 QueryOver 中进行了一些更改

var productsWithLatestComments = _sessionHelper.GetSession().QueryOver(() => p)
                .JoinAlias(() => p.Comments, () => cm, JoinType.LeftOuterJoin, Subqueries.Where(() => cm.CommentDate == QueryOver.Of<Comment>()
                    .Where(c => c.Product.Id == p.Id)
                    .SelectList(list => list.SelectMax(c => c.CommentDate)).As<DateTime>()))
                .Where(() => p.Status != "NOT SOLD" )
                .SelectList(list => list ....GET THE LIST OF COLS.........