QueryOver: select ... where property in (...)

Question

I try to use queryover to represent the following sql:

select * from Table1 t1 where t1.foreign_key in (select t2.Id from Table2 t2 where (...))

So I created a subquery for the inner select statement like this:

 var sq = QueryOver.Of<Table2>().Where(...).Select(c => c.Id);

However when I cannot use this subquery in the following query:

var query = QueryOver.Of<Table1>().WithSubquery.
 WhereProperty(t1 = t1.foreign_key).In(contactSubQuery);

I think the problem is that QueryOver expects a subquery over Table1 instead of Table2 in contactSubQuery, but then I cannot access the required properties of Table2. In How do I express a query containing a WHERE..IN subquery using NHibernate's QueryOver API? a similar problem is addressed (using JoinAlias), but I cannot see how to apply that solution for my case. Thanks for any help!

SOLUTION:

Thanks alot @Radim, you were almost right. I was already using

Queryover.Of<T>()

in the query but the problem was that I was assigning it to a IQueryOver variable (since we have a no var-keyword styleguide in our company). After I assigned it to var it compiled. Since I did not expect this to cause the problem at all I simplified every variable to var in the question, so the posted code should actually already have worked lol... I checked the type and simply changed the query to (in accordance with the no-var rule):

QueryOver<Table1> = QueryOver.Of<Table1>()
        .WithSubquery
           .WhereProperty(t1 => t1.foreign_key)
           // won't compile, because passed is IQueryOver<T,T>, 
           // not the QueryOver<U>   
           .In(subquery)

where before I had...

IQueryOver<Table1, Table1> = ...

Again, thanks alot for the help!

I can see, that you've updated the question, so my answer is now maybe not accurate. But anyhow, as shown [here](http://stackoverflow.com/a/20537260/1679310) we can nest subqueries as we like. And the syntax then is still the same... Does it help? Or did I miss your real issue? — Radim Köhler, Sep 29 '14 at 08:40
I´m sry, IsIn was only used because I experimented around with it and copied it from my solution. Using only .In() however, I still cannot compile: The compiler tells me something like: "type arguments for method TReturn (...).QueryOverSubqueryPropertyBuilderBase<...>.In(QueryOver) cannot be inferred from usage. Try specifying the arguments explicitly". I think I either have a wrong conversion in the subquery to a list of integers / ids or there is a problem with using a subquery of type A in another query of type B. — Philipp, Sep 29 '14 at 08:49
I guess that you are passing the `session.QueryOver` instead of `QueryOver.Of`. I updated my answer with more explanation. We are close, I am sure ;) — Radim Köhler, Sep 29 '14 at 09:00

Radim Köhler · Accepted Answer · 2014-09-29T08:59:57.597

You are almost there, just the syntax is not like this:

var query = QueryOver.Of<Table1>().WithSubquery.
    WhereProperty(t1 = t1.foreign_key).IsIn(contactSubQuery);

but:

 // subquery "sq"
 var sq = QueryOver.Of<Table2>().Where(...).Select(c => c.Id); 
 var query = QueryOver.Of<Table1>()
            .WithSubquery
               .WhereProperty(t1 => t1.foreign_key)
               .In(sq) // instead of .IsIn(contactSubQuery)
            ...

Because .IsIn() is a general extension method:

/// <summary>
/// Apply an "in" constraint to the named property
///             Note: throws an exception outside of a QueryOver expression
/// 
/// </summary>
public static bool IsIn(this object projection, ICollection values);

while .In() is the method of the returned result "QueryOverSubqueryPropertyBuilderBase" (the result of the .WhereProperty() call)

Also, be sure, that the passed argument into .In(subquery) is a QueryOver.Of<T>(). For example this is wrong:

var subquery = session.QueryOver<T>(); // it is named subquery
// but it is not of a type QueryOver<T>, but of a type
// IQueryOver<T, T>
// which is not what is expected here

 var query = QueryOver.Of<Table1>()
            .WithSubquery
               .WhereProperty(t1 => t1.foreign_key)
               // won't compile, because passed is IQueryOver<T,T>, 
               // not the QueryOver<U>   
               .In(subquery)
            ...

And that will produce the:

Error 1 The type arguments for method 'NHibernate.Criterion.Lambda.QueryOverSubqueryBuilderBase<NHibernate.IQueryOver<>.... cannot be inferred from the usage. Try specifying the type arguments explicitly.

QueryOver: select ... where property in (...)

1 Answers1