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!

Do you really need to use Criteria? HQL might be a better fit. — Diego Mijelshon, Nov 09 '10 at 00:47
Honestly, for these types of queries - save your hair and use HQL or just plain-old SQL (with CreateSqlQuery). — cbp, Nov 09 '10 at 03:29
Since the subquery results in a collection of scalars instead of mapped entities, how would I express the join in HQL? — David Rubin, Nov 09 '10 at 21:40

score 6 · Answer 1 · edited May 23 '17 at 11:58

6

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

edited May 23 '17 at 11:58

Community

1
1

answered Jul 01 '11 at 15:59

MonkeyCoder

2,600
2
28
30

Mark Perry · Answer 2 · 2011-07-01T17:39:51.480

1

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)
)

edited Jul 01 '11 at 17:39

answered Jun 30 '11 at 08:56

Mark Perry

1,705
10
12

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? – David Rubin Jun 30 '11 at 18:17
@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. – Mark Perry Jul 01 '11 at 07:41
@David Rubin - With help from MoneyCoder I have a criteria version of your query which gives the same results. – Mark Perry Jul 01 '11 at 16:30
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.." - you just replaced Item with Product but forgot about this one). I think some moderator should be informed. Bounty is a bounty! Definitely downvote. – IamDeveloper Jul 01 '11 at 17:19
1

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. – Mark Perry Jul 01 '11 at 17:43
Sure. So what's the point in copying the code from his response into yours? I see this happening in SO a lot recently. – IamDeveloper Jul 01 '11 at 19:32
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. – Mark Perry Jul 01 '11 at 19:47

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

2 Answers2

Linked