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 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
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<U>(QueryOver<U>) 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
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

Radim Köhler Radim Köhler · Accepted Answer · 2014-09-29T08:32:52

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 Answers