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?

MonkeyCoder · Accepted Answer · 2011-07-06T07:04:46.740

14

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.

edited Jul 06 '11 at 07:04

answered Jul 01 '11 at 07:24

MonkeyCoder

2,600
2
28
30

I'm a little confused. Is there a way to do this with the ICriteria API instead of QueryOver? I've not used QueryOver before, and this may be a complicated 'first example' :\ – GWLlosa Jul 01 '11 at 14:14
@GWLlosa: Can you confirm that the sql that I've posted in my answer works for you and that you get exactly the same results? I can than try to re-write the query with the Criteria API, btw which version of NHibernate are you using? – MonkeyCoder Jul 01 '11 at 14:46
The SQL you have and the SQL I have do in fact produce the same rows. I'm using NHibernate 3.1.0.4000, according to the DLL details. – GWLlosa Jul 01 '11 at 14:52
@GWLlosa: Please check my update with the Criteria API version. – MonkeyCoder Jul 01 '11 at 15:14
Awesome work! The key insight I was missing was that I could add a EqProperty restriction between a property and a Projection. We're using ICriteria for consistency with existing code that also uses ICriteria; otherwise I agree; QueryOver is a bit cleaner. – GWLlosa Jul 01 '11 at 15:36
Can you explain how to attach the subquery when using QueryOver? I tried session.QueryOver(() => p).WithSubquery.WhereExists(subquery) but it throws an exception that the parent property was not found. (i.e. p.ChildId) – H77 Jul 04 '11 at 09:19

Selecting on Sub Queries in NHibernate with Critieria API

1 Answers1

Linked