NHibernate HQL SELECT TOP in sub query
hql, nhibernate
Solution
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.
Problem
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?