How to a NHibernate subquery in Where clause with LINQ?

Question

I'm trying to write a correlated subquery in the where clause like this:

var foo = from d in session.Query<Document>()
          where true == 
              ( from a in session.Query<ACLEntry>()
                where a.Id == d.Id || a.Id == null
                select a.Result
              ).FirstOrDefault()
          select d;

The expected SQL output is very similar to this unanswered question on SO.

I think the Linq statement itself is fine because I can get it to run in LinqPad where I was prototyping. But NHibernate throws me these mysterious errors:

ERROR NHibernate.Hql.Parser [(null)] - NoViableAltException(86@[])

ERROR NHibernate.Hql.Parser [(null)] - MismatchedTreeNodeException(72!=3)

Is this an unsupported scenario with the NHibernate LINQ provider? Any ideas on how I might be able to restructure this query to get around it?

score 0 · Answer 1 · answered Mar 04 '13 at 17:07

0

Try this instead :

var foo = from d in session.Query<Document>()
          where (from a in session.Query<ACLEntry>()
                  where a.Id == d.Id || a.Id == null
                  select a.Result
                 ).FirstOrDefault() != null
          select d;

Hope this will help !!

answered Mar 04 '13 at 17:07

Kundan Singh Chouhan

13,952
4
27
32

That works (in the sense there are no NH errors), but the query it generates looks like this: `select * from Document where (select TOP 1 blah blah blah) is not null`. However, I need it to end with `= 1` instead. But that's pretty much back to my original query and error. – Ragesh Mar 04 '13 at 17:28

rae1 · Answer 2 · 2013-03-05T18:20:39.437

0

It is probably having some trouble parsing the true == ... portion of the query.

Might want to try this instead,

var foo = from d in session.Query<Document>()
    where (from a in session.Query<ACLEntry>()
           where a.Id == d.Id || a.Id == null
           select a.Result
          ).FirstOrDefault()
    select d;

edited Mar 05 '13 at 18:20

answered Mar 05 '13 at 01:02

rae1

6,066
4
27
48

I get what you're saying, but the thing is that `Result` is a bool in both my class and the database table so I don't understand why I can't query it that way. Having said that, it does indeed work if I change everything (both my class and database column) to an int and run the query like you've shown. What's special about `bool` that causes it to fail? Again, the exact same query works fine if I run it in LinqPad, so I'm inclined to think it's really something breaking in NHibernate's Linq provider. – Ragesh Mar 05 '13 at 08:55
In that case you don't need to specify `true == ...`. I updated my answer to express what I mean. – rae1 Mar 05 '13 at 18:19

How to a NHibernate subquery in Where clause with LINQ?

2 Answers2

Linked