NHibernate HQL SELECT TOP in sub query

hqlnhibernate

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?

Best Solution

From NHibernate 3.2 you could use SKIP n / TAKE n in hql at the end of the query. You query will be:

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