NHibernate的标准在哪里列表属性的任何元素为真 [英] NHibernate Criteria Where any element of list property is true

问题描述

我有一个已经创建NHibernate的标准查询，我需要修改，因此我可以添加一个新的条件。

查询是基于订单对象，其中有一个名单的OrderItems 然后，每个 OrderItem的拥有的布尔的名为 FinalDeliveryIndi​​cator 。

属性

在我的条件查询我需要补充的，我想所有订单的情况，它的的OrderItems 的 FinalDeliveryIndi​​cator BOOL设置为真正

查询，此刻是：

  VAR搜索= NHibernateSession.CreateCriteria（typeof运算（顺序））
 .CreateAlias（合同，C，JoinType.InnerJoin）
 .CreateAlias（C.Supplier，S，JoinType.InnerJoin）
。新增（Restrictions.Eq（Buyer.Id，companyId））
。新增（Restrictions.Eq（请将isDeleted ，FALSE））
。新增（Restrictions.Eq（IsActiveVersion，真））
 .SetFirstResult（paging.PageIndexSQL）
 .SetMaxResults（paging.PageSize）
。 AddOrder（SortOrder.Desc（ID））; 
  

现在，我需要补充一点的病情，我告诉你。该查询已被使用在这个应用程序的许多地方，所以我不能切换到的 QueryOver 的或一些其他类型的查询，由于崩溃的东西的风险。

< DIV CLASS =h2_lin>解决方案

我们需要为子选择。这可能与的子查询的实现。

15.8。独立查询和子查询

我们可以用的DetachedCriteria 定义子查询：

  VAR子查询= DetachedCriteria.For<&OrderItem的GT;（）
。新增（Restrictions.Eq（FinalDeliveryIndi​​cator，真））
 .SetProjection（Projections.Property（的OrderId））; 
  

这以后将最终成为这个SQL片段：

 （选择的OrderId FROM的OrderItems WHERE FinalDeliveryIndi​​cator = 1）
  

和这个子查询我们可以在主查询

  ... 
搜索作为WHERE的一部分使用。添加（Subqueries.PropertyIn（ID，子查询））
 ... 
  

哪些会增加此限制到WHERE子句：

  SELECT ... 
从订单THIS_ 
 JOIN .. 
 WHERE ... 
和this_.OrderId是//上面的子查询
（SELECT FROM的OrderId WHERE的OrderItems = FinalDeliveryIndi​​cator 1）
  

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

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

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.

The query, at the moment, is:

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"));

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.

解决方案

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

15.8. Detached queries and subqueries

We can define subquery with DetachedCriteria:

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

This would later end up as this SQL snippet:

(SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 )

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

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

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的标准在哪里列表属性的任何元素为真的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！