所以，我有以下结构的SQL查询：

So I have a SQL query with the following structure:

select p.* from
(
    select max([price]) as Max_Price,
    [childId] as childNodeId
    from [Items] group by [childId]
) as q inner join [Items] as p on p.[price] = q.[Max_Price] and p.[childId] = q.[childNodeId]

我需要重新创建此查询NHibernate的，使用标准的API。我尝试使用子查询的API，但似乎需要内部查询返回一个单列检查与外部查询中的属性平等。不过，我返回两个。我读过，这可以通过HQL API来实现，但我需要用标准的API做到这一点，因为我们要动态地产生这样的疑问的飞行。任何人都可以引导我在这里的正确方向？

I need to recreate this query in NHibernate, using the Criteria API. I tried using the Subqueries API, but it seems to require that the inner query returns a single column to check equality with a property in the outer query. However, I return two. I've read that this can be accomplished via the HQL API, but I need to do it with Criteria API, as we're going to be dynamically generating queries like this on the fly. Can anyone steer me in the correct direction here?

推荐答案

我已经设法通过略微调整了原来的SQL查询来解决类似的问题。我已经结束了像这样（伪SQL code）：

I've managed to resolve a similar problem by slightly adapting the original sql query. I've ended up with something like this (pseudo sql code):

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实现：

And this is the QueryOver implementation:

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的能正确解析实体（伪code）：

From here you only need to pass the aliases so that NHibernate can resolve entities correctly (pseudo code):

var filter = QueryOver.Of(() => p)
    .WithSubquery.WhereExists(GetSubQuery(p, criteria...));

我希望这有助于在特定的情况下。

I hope this helps in your particular case.

更新：标准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版）。

Nevertheless I would recommend sticking to the QueryOver version, it's much more intuitive and you avoid magic strings (especially that you don't have to upgrade the NH version).

请让我知道，如果这是为你工作。

Please let me know if this is working for you.