我成功地解决了一个类似的问题,通过稍微调整原始的SQL查询语句。最终我得到了像这样的东西(伪代码):
SELECT p.* FROM [Items] as p
WHERE EXISTS
(
SELECT [childId] as childNodeId FROM [Items] as q
WHERE p.[childId] = q.[childNodeId]
GROUP BY q.[childId]
HAVING p.[price] = MAX(q.[price])
)
这是QueryOver实现:
var subquery = QueryOver.Of(() => q)
.SelectList(list => list.SelectGroup(() => q.ChildId))
.Where(Restrictions.EqProperty(
Projections.Property(() => p.Price),
Projections.Max(() => q.Price)))
.And(Restrictions.EqProperty(
Projections.Property(() => p.ChildId),
Projections.Property(() => q.ChildId)));
从这里开始,你只需要传递别名,这样NHibernate才能正确解析实体(伪代码):
var filter = QueryOver.Of(() => p)
.WithSubquery.WhereExists(GetSubQuery(p, criteria...));
希望这能对你的特定情况有所帮助。
更新:Criteria API
var subquery = DetachedCriteria.For<Items>("q")
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("q.ChildId")))
.Add(Restrictions.EqProperty("p.Price", Projections.Max("q.Price")))
.Add(Restrictions.EqProperty("p.ChildId", "q.ChildId"));
var query = DetachedCriteria.For<Items>("p")
.Add(Subqueries.Exists(subquery));
不过我建议坚持使用 QueryOver
版本,它更加直观,并且避免了神奇的字符串(特别是您不必升级 NH 版本)。
如果这对您起作用,请告诉我。