How can I express joining to a grouped subquery using NHibernate?

Question

I'm trying to express a SQL query using NHibernate's Criteria API, and I'm running into difficulty because I'm thinking in a database-centric way while NHibernate is object-centric.

SQL (works great):

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
inner join
    (select max(innerT.id)
     from tbl innerT
     group by innerT.col1) grpT
on outerT.id = grpT.id

Essentially, this is a self-join of a table against a subset of itself. I suppose I could try turning the self-join into a restriction:

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
where outerT.id in (select max(innerT.id) from tbl innerT group by innerT.col1)

But I'm not sure how to express that using NHibernate either; I'm fighting with the DetachedCriteria's ProjectionList and wanting to select only max(id) while grouping by col1.

Thanks so much for your suggestions!

Latrice

Do you really need to use Criteria? HQL might be a better fit. – Damascene

Honestly, for these types of queries - save your hair and use HQL or just plain-old SQL (with CreateSqlQuery). – Mousy

Since the subquery results in a collection of scalars instead of mapped entities, how would I express the join in HQL? – Latrice

Answer 1

I don't know if I should post this as a new answer or to add it as a comment on the original question, but I think I've resolved a similar problem in this thread:

Selecting on Sub Queries in NHibernate with Critieria API

Unlookedfor

Answer 2

AFAIK you cannot join to subqueries at all in NHibernate but you can re-organise the query to use either an EXISTS or IN clause to replicate the same functionality.

I realise the question asks for this to be done using the Criteria API but I thought I would post a HQL version which may give someone else some ideas.

var results = session.CreateQuery("from Product p where p.Id in (
    select max(p2.id)
from Product p2
group by p2.col1
)")

I also found this JIRA issue surrounding the Criteria API and not including group by columns in the select. Currently it looks like what you want cannot be achieved using the Criteria API at all.

Group By Property without adding it to the select clause

UPDATE Using the example from Monkey Coders post looks like you can do this:

var subquery = DetachedCriteria.For<Product>("p")
.SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("p.Col1"))
.Add(Restrictions.EqProperty("p2.Id", Projections.Max("p.Id"));

var query = DetachedCriteria.For<Product>("p2")
.Add(Subqueries.Exists(subquery));

Which would produce the following SQL

select *
from Product p2
where exists (
    select p.col1
    from Product p
    group by p.col1
    having p2.Id=max(p.Id)
)

Manard

Thanks for jumping in. What you posted is a good HQL translation of my second version (using IN), but I'm wondering what the HQL would look like for the JOIN version. Any deep thoughts? – Latrice

@David Rubin - Currently NHibernate does not support those types of joins (e.g. Joins to SubQueries in HQL, Criteria, QueryOver or LINQ). Although the IN clause is going to give you almost the same query plan. – Manard

@David Rubin - With help from MoneyCoder I have a criteria version of your query which gives the same results. – Manard

Mark Perry - it seems to me that you're trying to get bounty reward using MonkeyCoder answer. The code is very similiar, I'm pretty sure you copied the code directly ("var query = DetachedCriteria.For<Items>.." - you just replaced Item with Product but forgot about this one). I think some moderator should be informed. Bounty is a bounty! Definitely downvote. – Plaque

I gave Monkeycoder credit for the idea both in my answer and by commenting on his answer as well as upvoting his answer in the answer he links to. I simply updated my answer based on his work. Not sure I deserve downvote for that. plus i tried to answer the question using hql before monkeycoder posted his answer. – Manard

Sure. So what's the point in copying the code from his response into yours? I see this happening in SO a lot recently. – Plaque

MonkeyCoders example in the referenced question is different from the example given by the op. I merely credited and thanked then adjusted the example to suit the question above. – Manard