c# - Selecting on Sub Queries in NHibernate with Critieria API -

so have sql query following structure:

select p.* (     select max([price]) max_price,     [childid] childnodeid     [items] group [childid] ) q inner join [items] p on p.[price] = q.[max_price] , p.[childid] = q.[childnodeid] 

i need recreate query in nhibernate, using criteria api. tried using subqueries api, seems require inner query returns single column check equality property in outer query. however, return two. i've read can accomplished via hql api, need criteria api, we're going dynamically generating queries on fly. can steer me in correct direction here?

i've managed resolve similar problem adapting original sql query. i've ended (pseudo sql code):

select p.* [items] p exists (     select [childid] childnodeid [items] q     p.[childid] = q.[childnodeid]     group q.[childid]      having p.[price] = max(q.[price]) ) 

and 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 need pass aliases nhibernate can resolve entities correctly (pseudo code):

var filter = queryover.of(() => p)     .withsubquery.whereexists(getsubquery(p, criteria...)); 

i hope helps in 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 recommend sticking queryover version, it's more intuitive , avoid magic strings (especially don't have upgrade nh version).

please let me know if working you.