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?
See Question&Answers more detail:os