NHibernate Criteria 其中列表属性的任何元素为真 [英] NHibernate Criteria Where any element of list property is true

问题描述

我已经创建了一个 NHibernate Criteria 查询，我需要修改它以便添加新条件.

I have an already created NHibernate Criteria query that I need to modified so I can add a new condition.

查询基于 Order 对象，该对象有一个 OrderItems 列表，然后，每个 OrderItem 都有一个 bool 名为 FinalDeliveryIndi​​cator 的属性.

The query is based on the Order object, which has a list of OrderItems and then, every OrderItem has a bool property named FinalDeliveryIndicator.

在我的 Criteria 查询中，我需要添加一个条件，在该条件中，我希望所有订单中至少有一个 OrderItems 将 FinalDeliveryIndi​​cator bool 设置为 true.

In my Criteria query I need to add a condition in which I want all orders that at least one of its OrderItems has the FinalDeliveryIndicator bool set to true.

目前的查询是:

var search = NHibernateSession.CreateCriteria(typeof(Order))
            .CreateAlias("Contract", "C", JoinType.InnerJoin)
            .CreateAlias("C.Supplier", "S", JoinType.InnerJoin)
            .Add(Restrictions.Eq("Buyer.Id", companyId))
            .Add(Restrictions.Eq("IsDeleted", false))
            .Add(Restrictions.Eq("IsActiveVersion", true))
            .SetFirstResult(paging.PageIndexSQL)
            .SetMaxResults(paging.PageSize)
            .AddOrder(SortOrder.Desc("Id"));

现在我需要添加我告诉过你的条件.此查询已在此应用程序的许多地方使用，因此我无法切换到 QueryOver 或某些其他类型的查询，因为存在崩溃的风险.

Now I need to add that condition I told you about. This query is already in use at many places on this application and so I cannot switch to QueryOver or some other type of query, due to the risk of crashing something.

推荐答案

我们需要的是 Sub-SELECT.这可以通过子查询来实现.

What we would need is Sub-SELECT. This could be achieved with subquery.

我们可以使用DetachedCriteria来定义子查询:

We can define subquery with DetachedCriteria:

var subquery = DetachedCriteria.For<OrderItem>()
    .Add(Restrictions.Eq("FinalDeliveryIndicator", true))
    .SetProjection(Projections.Property("OrderId"));

这稍后会变成这个 SQL 片段:

This would later end up as this SQL snippet:

(SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 )

这个子查询我们可以在主查询中作为 WHERE 的一部分使用

And this subquery we can use as a part of WHERE in our main query

...
search.Add(Subqueries.PropertyIn("Id", subquery))
...

将这个限制添加到 WHERE 子句中:

Which would add this restriction into WHERE clause:

SELECT ...
FROM Order this_
JOIN ...
WHERE ...
AND this_.OrderId IS IN // the above subquery
        (SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 )

这篇关于NHibernate Criteria 其中列表属性的任何元素为真的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！