NHibernate HQL SELECT TOP在子查询中 [英] NHibernate HQL SELECT TOP in sub query

问题描述

是否可以在子查询中使用SetMaxResult()?我正在编写查询以返回属于最新订单的所有订单商品.因此，我需要限制子查询上的记录数.

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.

等效的sql看起来像这样:

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)

我之所以使用hql是因为条件api不允许您投影另一个域对象(我在查询订单，但想返回订单商品)

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

我知道hql不接受"SELECT TOP"，但是如果我使用SetMaxResult()，它将应用于外部查询，而不是子查询.

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

有什么想法吗?

推荐答案

只需查询订单(并使用SetMaxResult)并执行提取联接"以确保立即加载所选订单的所有订单项. 然后，在返回的订单上，您可以访问订单项目，而不会导致将新的SQL语句发送到数据库.

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.

这篇关于NHibernate HQL SELECT TOP在子查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！