Using a subselect in a join using NHibernate

Question

I would like to write the following SQl in NHibernate - Detached Criteria if possible.

select * from parent 
INNER JOIN child on parent.id=child.parentid 
INNER JOIN 
  (select ChildID, MAX(ChildDate) MaxChildDate from child group by ChildID) max
ON child.childid, child.ChildDate=max.MaxChildDate

This gives me the latest child in every paret.

I can write the sub-query in Critera but cannot perform the double link of ChildID and MaxDate.

Answer 1

I can't say I actually know a way of doing what you are asking, NHibernate returns the actual object that you're querying, in this case it would return a list/set of Parent objects. This Parent object would then have a collection of Child objects attached to it. The concept of linking to this data twice and pulling different information doesn't work in NHibernate as far as I am aware.

If you require this to be available directly on the Parent, I would suggest creating another variable on the Parent object which has these details pre-filtered via the "where" attribute in your Parent.hbm.xml file. If you don't wish to do this I would suggest using LINQ or something similar to pull out the latest Child object for each Parent in your collection.

An example of the "where" attribute that you would find in your Parent.hbm.xml file is:

<set name="LatestChild" table="child" generic="true" inverse="true" where="*your sub-query here*">
  <key column="parent_id" />
  <one-to-many class="YourNameSpace.Model.Child,Model"/>
</set>

This will only ever return the Child objects which match the where query of the collection.

You can find documentation on this here. Although this is the Hibernate reference and not NHibernate it is almost identical in most areas.

Good luck!

Answer 2

I would create a sorted list property on the parent (sorted by ChildDate), so when you get last (with linq extensions possibly), you get the one you need. No need to create complicate joins.

Answer 3

Thanks, the join wasn't quite as simple as the example and there were a lot of tables being used - fine if done in a SQL set based setup but there would have been a ridiculous amount of data coming back to filter it in linq.

Unfortunately speed had to win over design so I created a view with the complex join in and used that as a table for a dictionary on the primary domain object. Thanks for all your help though.

Cheers

Stu