Subroutine selection in NHibernate using the Critieria API

Question

So, I have an 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 subquery API, but it seems that the inner query returns a single column to check for equality with the property in the outer query. However, I return two. I read that this can be done using the HQL API, but I need to do this using the API criteria, as we will dynamically generate queries like this on the fly. Can someone direct me in the right direction here?

Answer 1

I managed to solve a similar problem by slightly adapting the original SQL query. I 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 implementation of 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))); 

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

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

Hope this helps in your specific 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)); 

However, I would recommend sticking with the QueryOver version, it is much more intuitive and you avoid magic lines (especially that you do not need to update the NH version).

Please let me know if this works for you.