How to do this in nhibernate queryover

Question

I need to do this in nhibernate QueryOver:

select distinct sc.* from [Security].[Commands] sc
inner join [Security].[SystemSubjects] ss on ss.Id = sc.Id
left outer join [Security].[SystemSubjectRoles] ssr on ssr.SystemSubjectId = ss.Id
left outer join [Security].[Roles] sr on sr.Id = ssr.RoleId
left outer join [Security].[UserRoles] ur on ur.IdRole = sr.Id 
left outer join [Security].[User] su2 on su2.Id = ur.IdUser
left outer join [Security].SystemSubjectUsers ssu on ssu.SystemSubjectId = ss.Id
left outer join [Security].[User] su on su.Id = ssu.UserId
where (su2.Id = 1 or su.Id = 1)

So far I have done this:

var queryOverRoles = QueryOver.Of<Command>(() => cmdAlias)
            .JoinAlias(() => cmdAlias.SystemSubjectRoles, () => ssRoleAlias, JoinType.LeftOuterJoin)
            .JoinAlias(() => ssRoleAlias.Role, () => roleAlias, JoinType.LeftOuterJoin)
            .JoinAlias(() => roleAlias.Users, () => userAlias, JoinType.LeftOuterJoin)
            .JoinAlias(() => cmdAlias.SystemSubjectUsers, () => ssUserAlias, JoinType.LeftOuterJoin)
            .JoinAlias(() => ssUserAlias.User, () => userAlias2, JoinType.LeftOuterJoin)
            .TransformUsing(Transformers.DistinctRootEntity)
            .Where(() => ssRoleAlias.SecurityPermission == SecurityPermission.Allow)
            .And(() => (userAlias.Id == userId || userAlias2.Id == userId))

but this selects all (Select ) and I want only the Command object (select sc.). If I try to add a projection to the query like this:

queryOverRole.Select(x=>x);

it throws this exception:

System.Exception was caught   Message=Could not determine member

from x Source=NHibernate
StackTrace: at NHibernate.Impl.ExpressionProcessor.FindMemberExpression(Expression expression) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\ExpressionProcessor.cs:line 189 at NHibernate.Criterion.QueryOver2.Select(Expression1[] projections) in d:\CSharp\NH\nhibernate\src\NHibernate\Criterion\QueryOver.cs:line 363 at Novatec.Persistence.Repositories.Implementations.UserRepository.GetAllowedUserCommands(Int32 userId) in C:\Projects\EGrad\Main\Source\Novatec.E-Grad\Source\Novatec.Persistence\Repositories\Implementations\UserRepository.cs:line 140 at Novatec.Administracija.Services.Security.SecurityManager.AuthorizeUserAction(Int32 userId, String commandId) in C:\Projects\EGrad\Main\Source\Novatec.E-Grad\Source\Novatec.Administracija.Services\Security\SecurityManager.cs:line 48 at Novatec.Framework.Infrastructure.Services.ServiceBase.ExecuteCommand(Int32 userId, String appId, ICommand command) in C:\Projects\EGrad\Main\Source\Novatec.E-Grad\Source\Novatec.Framework.Infrastructure\Services\ServiceBase.cs:line 94 InnerException:

Answer 1

Try this.... you need to specify which alias to select. queryOverRole.Select(result => cmdAlias);