NHibernate QueryOver WithSubQuery WhereAll clause

Question

Houston we have a problem... No I guess its just me. :)

I am trying to do a Subquery on an NHibernate QueryOver object. Queries without SubQueries work fine, but when a SubQuery is added everything falls apart.

EDIT1:
What I want to accomplish is to select all users where all the relations to containers have the AccountStatus property set to DELETED

var queryOver = session.QueryOver<User>();

queryOver.WhereRestrictionOn(x => x.UserHasContainer).IsNotEmpty
         .JoinQueryOver<ContainerUser>(x => x.UserHasContainer)
         .WithSubquery.WhereAll(x => x.AccountStatus == AccountStatus.DELETED);

Keep getting this exception:

System.Exception: right operand should be detachedQueryInstance.As<T>() - "DELETED"
at NHibernate.Impl.ExpressionProcessor.FindDetachedCriteria(Expression expression)
at NHibernate.Impl.ExpressionProcessor.ProcessSubqueryExpression(LambdaSubqueryType subqueryType, BinaryExpression be)
at NHibernate.Impl.ExpressionProcessor.ProcessSubquery[T](LambdaSubqueryType subqueryType, Expression`1 expression)
at NHibernate.Criterion.Subqueries.WhereAll[T](Expression`1 expression)
at RBAC.Infrastructure.DataAccess.QueryObject.Implementation.UserQueryObject.FilterByContainerRelationsAreScheduledForDeletion(IQueryOver`2 queryOver) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Implementation\UserQueryObject.cs:line 113
at RBAC.Infrastructure.DataAccess.QueryObject.Implementation.UserQueryObject._ExecuteQuery(IQueryOver`2 queryOver) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Implementation\UserQueryObject.cs:line 101
at RBAC.Infrastructure.DataAccess.QueryObject.Base.BaseQueryObject`1.ExecuteQuery(ISessionDecorater session) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Base\BaseQueryObject.cs:line 20
at RBAC.Infrastructure.DataAccess.GenericDAO.Implementation.GenericDAO.GetByQueryObject[T](IQueryObject`1 queryObject) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\GenericDAO\Implementation\GenericDAO.cs:line 253
at RBAC.Infrastructure.BusinessService.CleanupModule.Implementation.RBACCleanerActions.CleanUpUsers() in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\BusinessService\CleanupModule\Implementation\RBACCleanerActions.cs:line 59

I have googled up, down and sideways for right operand should be detachedQueryInstance and other queries as well with no luck. Hope somebody here knows what is wrong, and how to solve theese issues in the future.

I have also tried this query with no luck:

queryOver.WhereRestrictionOn(x => x.UserHasContainer).IsNotEmpty
         .JoinQueryOver<ContainerUser>(x => x.UserHasContainer)
         .Where(Subqueries.WhereAll<ContainerUser>(x => x.AccountStatus == AccountStatus.DELETED));

Exception:

System.InvalidCastException: Unable to cast object of type 'System.Linq.Expressions.PropertyExpression' to type 'System.Linq.Expressions.BinaryExpression'.
at NHibernate.Impl.ExpressionProcessor.ProcessSubquery[T](LambdaSubqueryType subqueryType, Expression`1 expression)
at NHibernate.Criterion.Subqueries.WhereAll[T](Expression`1 expression)
at RBAC.Infrastructure.DataAccess.QueryObject.Implementation.UserQueryObject.FilterByUsersWereAllTheirFunctionRelationsAreScheduledForDeletion(IQueryOver`2 queryOver) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Implementation\UserQueryObject.cs:line 157
at RBAC.Infrastructure.DataAccess.QueryObject.Implementation.UserQueryObject._ExecuteQuery(IQueryOver`2 queryOver) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Implementation\UserQueryObject.cs:line 97
at RBAC.Infrastructure.DataAccess.QueryObject.Base.BaseQueryObject`1.ExecuteQuery(ISessionDecorater session) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\QueryObject\Base\BaseQueryObject.cs:line 21
at RBAC.Infrastructure.DataAccess.GenericDAO.Implementation.GenericDAO.GetByQueryObject[T](IQueryObject`1 queryObject) in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\DataAccess\GenericDAO\Implementation\GenericDAO.cs:line 253
at RBAC.Infrastructure.BusinessService.ADModule.Implementation.DeletionFlagSetter.FlagUsersForDeletionWereAllRelationsAreFlaggedForDeletionForADRegisteredUsers() in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\BusinessService\ADModule\Implementation\DeletionFlagSetter.cs:line 115
at RBAC.Infrastructure.BusinessService.ADModule.Implementation.ActiveDirectorySynchronizer.Synchronize() in c:\APPL\dev\RBAC\Dev\RBAC.Infrastructure\BusinessService\ADModule\Implementation\ActiveDirectorySynchronizer.cs:line 156

EDIT2:
I think that the equivalent in linq would be this, if i had a list of users.

users.Where(x => x.UserHasFunctions.All(y => y.IsDeleted)).ToList();

Note: You are mixing up Linq and QueryOver here. QueryOver might look like Linq but it's a completely different thing. — cremor
thank you for the note, i removed everything about linq and added better tags for the question — furier
The error message is saying that rather than comparing the left hand side with a scalar value, you should be using a DetachedCritiera or DetachedQueryOver. From your (deleted) answer below it looks like you might have figured this out. Do you still need some help? — Andrew Whitaker
I think you're right--could you post the SQL you want to generate by any chance? — Andrew Whitaker
@AndrewWhitaker, no i don't have the sql but i wrote an equivalent linq query for it at the bottom of the question. PS: A simple Where doesn't give me what I need. If a User have 10 relations, and only 1 of them have the AccountStatus set to DELETED, the user will get selected. But I only want to select the user if all 10 have the AccountStatus set to DELETED. — furier

Andrew Whitaker Andrew Whitaker · Accepted Answer · 2013-06-27T12:48:27

The first step I usually take when trying to work through a QueryOver query is to come up with the correct SQL. Based on your LINQ query, I think that would look something like this:

select
    User.*
from
    User
where
    /* Or whatever "deleted" turns out to be, not necessarily '1' */
    1 = all (select AccountStatus          
             from ContainerUser
             where ContainerUser.UserId = User.Id);

Basically get all users where all of that user's ContainerUser rows have an AccountStatus of deleted.

So in QueryOver you could write something like this:

User userAlias = null;

session.QueryOver<User>(() => userAlias)
    .WithSubquery.WhereValue(AccountStatus.DELETED).EqAll(QueryOver.Of<ContainerUser>()
        .Where(uc => uc.User.Id == userAlias.Id)
        .Select(uc => uc.AccountStatus));

// select list, more restrictions, etc.

NHibernate QueryOver WithSubQuery WhereAll clause

1 Answers