Selecting on Sub Queries in NHibernate with Critieria API

Question

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]

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?

Answer 1

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])
)

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)));

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.

UPDATE: 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));

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.