CODEWITHSUNDEEP
c#nhibernatequeryover
furier
furier

Reputation: 1977

NHibernate QueryOver WithSubQuery WhereAll clause

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

Upvotes: 4

Views: 3715

Answers (1)

Andrew Whitaker
Andrew Whitaker

Reputation: 126042

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.

Upvotes: 5

Related Questions