NHibernate HQL SELECT TOP in sub query

Question

Is there a way of using SetMaxResult() on a sub query? Im writing a query to return all the order items belonging to the most recent order. So I need to limit the number of records on the sub query.

The equivalent sql looks something like:

SELECT i.*
FROM tbl_Orders o
JOIN tbl_OrderItems i on i.OrderId = o.Id
WHERE
o.Id in (SELECT TOP 1 o.Id FROM tbl_Orders o orderby o.Date desc)

Im using hql specifically because criteria api doesnt let you project another domain object (Im querying on orders but want to return order items)

I know that hql doesnt accept "SELECT TOP", but if I use SetMaxResult() it will apply to the outer query, not the subquery.

Any ideas?

Answer 1

Just query the orders (and use SetMaxResult) and do a 'fetch join' to ensure all orderitems for the selected orders are loaded straight away. On the returned orders you can then access the order items without this resulting in a new SQL statement being sent to the database.