NHibernate Query, using OfType and Subqueries

Question

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.

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;

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:

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)))), ), )]

using Nhibernate version 3.1.0.4000

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

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

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;

although I'd still prefer an NHibernate solution.

Which version of NH do you use? Also you can try to use Any instead of Where and Count and is instead of OfType just to check if it works — Nikolay, Apr 17 '12 at 10:08
@Nikolay How do I change OfType to Count? Count doesn't return IQueryable and it can't perform the same check as OfType? — James, Apr 17 '12 at 10:58
I mean OfType can be changed to Is like this: `ThisOrder.Items.Any(i => i is SourceOrderItem && i.Source == Source)`. But it won't work if Source is declared on SourceOrderItem. But i think even filtering by type with OfType should work. I will check it in our project, because I think we use it. — Nikolay, Apr 17 '12 at 11:43
@Nikolay, I've updated the question with the results of your suggestion. — James, Apr 19 '12 at 11:23

score 0 · Accepted Answer · answered Apr 25 '12 at 10:14

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 Query, using OfType and Subqueries

1 Answers1

Linked