NHibernate查询，使用OfType和子查询 [英] NHibernate Query, using OfType and Subqueries

问题描述

我正在尝试编写NHibernate查询，该查询确定指定的记录是否与请求的源有关.在这种情况下，指定的记录是客户，并且如果客户有来自指定来源的任何订单，则该客户是相关的.

I'm trying to write a NHibernate Query which determines if a specified record is relevant to source of the request. In this case the specified record is a customer and a customer is relevant if they have any orders sourced from the specified source.

我已经试过了，但是以下查询失败，并显示QuerySyntaxException.

I've had a go but the following query fails with a QuerySyntaxException.

bool IsRelevant = Session.Query<Order>().Where
(
    ThisOrder => ThisOrder.Customer.ID == ThisCustomer.ID
    &&
    ThisOrder.Items.OfType<SourceOrderItem>().Where
    (
        I => I.Source.ID == Source.ID
    ).Count() > 0
).Count() > 0;

我认为问题的至少部分原因是SourceOrderItem是OrderItem的子类型，并且订单可能包含非该类型的项目，因此查询需要额外的过滤以仅查看那些是正确的类型.

I think at least part of the problem is that the SourceOrderItem is a sub type of OrderItem and the order may contain items which are not of that type, so the query needs additional filtering to only look at items which are of the correct type.

例外:

Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. [.Count[Domain.Order](.Where[Domain.Order](NHibernate.Linq.NhQueryable`1[Domain.Order], Quote((R, ) => (AndAlso(Equal(R.Customer.ID, p1), GreaterThan(.Count[Domain.SourceOrderItem](.Where[Domain.SourceOrderItem](.OfType[Domain.SourceOrderItem](R.Items, ), (I, ) => (Equal(I.Source.ID, p2)), ), ), p3)))), ), )]

使用Nhibernate版本3.1.0.4000

using Nhibernate version 3.1.0.4000

将其更改为使用Any而不是Where().Count > 0会导致SQL异常尝试运行查询

Changing the this to use Any instead of Where().Count > 0 results in an SQL Exception trying to run the Query

DECLARE @p0 int
DECLARE @p1 int
DECLARE @p2 int

SET @p0 = 1
SET @p1 = 1
SET @p2 = 1

select TOP (@p0) 
  request0_.ID as ID24_, 
  request0_.Date as Date24_, 
  request0_.Delivery as Delivery24_, 
  request0_.PO as PO24_, 
  request0_.Discount as Discount24_, 
  request0_.Notes as Notes24_, 
  request0_.PaymentType as PaymentT7_24_, 
  request0_.Transport as Transport24_, 
  request0_.Customer_id as Customer9_24_, 
  request0_.Site_id as Site10_24_, 
  request0_.CreatedBy_id as CreatedBy11_24_, 
  request0_1_.OrderNumber as OrderNum2_25_, 
  request0_2_.Revision as Revision26_, 
  request0_2_.ExpiryDate as ExpiryDate26_, 
  case 
    when request0_1_.Request_id is not null then 1 
    when request0_2_.Request_id is not null then 2 
    when request0_.ID is not null then 0 
  end as clazz_ 
from [Request] request0_ 
  left outer join [Order] request0_1_ on request0_.ID=request0_1_.Request_id 
  left outer join [Quote] request0_2_ on request0_.ID=request0_2_.Request_id 
where 
  request0_.Site_id=@p1 
  and 
  (exists 
    (select 
      items1_.ID 
     from 
       [RequestItem] items1_ 
     where request0_.ID=items1_.Request_id 
       and case 
         when items1_2_.ChargeableRequestItem_id is not null then 2 
         when items1_3_.ChargeableRequestItem_id is not null then 3 
         when items1_1_.RequestItem_id is not null then 1 
         when items1_4_.RequestItem_id is not null then 4 
         when items1_.ID is not null then 0 
       end=3 
       and items1_3_.Source_id=@p2))

由于第二个case语句下面的表均未声明，因此该语句似乎丢失了很多.

This seems to be missing quite a few from statements as none of the tables below second case statement are declared.

目前，我是通过自己编写SQL来解决此问题的.

For now I've worked around this by writing the SQL myself.

bool IsRelevant = ((Int32)Session.CreateSQLQuery(
    "Select COUNT(*) as IsRelevant From Request\r\n" +
    "Where\r\n" +
    "   Request.Site_Id = :p0\r\n" +
    "   And Request.ID in \r\n" +
    "(\r\n" +
    "Select Request_ID From RequestItem Where ID in\r\n" +
    "   (   \r\n" +
    "   Select SourceOrderItem.ChargeableRequestItem_id\r\n" +
    "   From SourceOrderItem\r\n" +
    "   Where SourceOrderItem.Source_id = :p1\r\n" +
    "   )\r\n" +
    ")"
    ).SetParameter("p0", S.ID).SetParameter("p1", Source.ID).UniqueResult()) > 0;

尽管我仍然更喜欢NHibernate解决方案.

although I'd still prefer an NHibernate solution.

推荐答案

可以通过拆分Nhibernate查询来做到这一点 例如

It is possible to do this by splitting the Nhibernate queries up e.g.

var Items = Session.Query<SourceOrderItem>().Where(I=> I.Source.ID == Source.ID).ToList();
foreach(var Item in Items)
{
    IsRelevant = Session.Query<Order>().Any(R => R.Customer.ID == C.ID && R.Items.Any(I => I.ID == Item.ID));
    if (IsRelevant)
        break;
}

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