Convert CASE WHEN FROM SQL to NHIBERNATE

Question

I have a SQL statement below that needs to convert it to NHibrnate QueryOver. I have searched the web but can't find a concrete solution. Anyone can help me on this?

SELECT 
  TABEL1.Id, 
  CASE WHEN EXISTS (SELECT Id FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID)
       THEN 'TRUE' 
       ELSE 'FALSE'
  END AS NewFiled  
FROM TABLE1


            --Here is the real POCO

                   public class UserRole 
                    {
                        [Required]
                        public virtual User User { get; set; }

                        [Required]
                        public virtual Role Role { get; set; }

                    }

                    public class UserTenant 
                    {

                        [Required]
                        public virtual Tenant Tenant { get; set; }


                        [Required]
                        public virtual User User { get; set; }

                    }


                    public class Role
                    {

                        public int Id {get;set}         
                        [StringLength(255), Required]
                        public virtual string RoleLabel { get; set; }

                        [StringLength(4000), Required]
                        public virtual string RoleDescription { get; set; }

                    }

                    public class User
                    {

                        public int Id {get;set}         
                        [StringLength(255), Required]
                        public virtual string Firstname { get; set; }

                        [StringLength(255), Required]
                        public virtual string Lastname { get; set; }

                    }


                   public class Tenant
                    {

                        public int Id {get;set}         
                        [StringLength(255), Required]
                        public virtual string Name { get; set; }

                        [StringLength(4000), Required]
                        public virtual string Description { get; set; }

                    }

             public class AssignRoleUsersModel
                {

                    public virtual int UserId { get; set; }

                    public virtual string LastName { get; set; }

                    public virtual string FirstName { get; set; }


                    public virtual bool IsAssigned { get; set; }



                }


    --This is the method to get the users assigned or not for that particular role.
   public RoleUsers GetRoleUsers(int Id)
                    {
             UserRole userRolesAlias = null;
             UserTenant userTenantsAlias = null;



         -- This query will get the role depending the Id that have passed. Take note that I don't want to use this query that is why I am converting it to queryOver.

                    var role = (from r in RoleRepository.Queryable()
                                where r.Id == Id
                                select r).FirstOrDefault();

        --This query will get all the users having the role result above.Take note that I don't want to use this query that is why I am converting it to queryOver.

                    var assignedUsers = UserRoleRepository.Queryable().Where(x => x.Role.Id == role.Id).Select(a => a.User.Id).ToArray();


        --This is the condition to know if the user was assigned to the role

                    var projection = Projections.Conditional(Restrictions.Where(() => userRolesAlias.User.Id.IsIn(assignedUsers))
            , Projections.Constant(true)
            , Projections.Constant(false)
            );

                    var users =
                        new List<AssignRoleUsersModel>(UnitOfWorkLocalData.CurrentUnitOfWork.Session.QueryOver(() => userTenantsAlias)
                                                    .Select(x => x.User.Id)
                                                    .Select(x => x.User.LastName)
                                                    .Select(x => x.User.FirstName)
                                                    .Select(x => x.User.UserName)
                                                    .Select(projection)
                                                    .TransformUsing(Transformers.AliasToBean<AssignRoleUsersModel>())
                                                    .List<AssignRoleUsersModel>());

        }

Answer 1

EXTENDED based on the extended question, the way how to do CASE WHEN:

// the ID of searched role, coming as parameter id
int searchedRolId = ...

UserRole userRolesAlias = null;

// this is the SUBQUERY we need, the inner select
var subquery = QueryOver
    .Of<UserRole>(() => userRolesAlias)
    .Where(() => userRolesAlias.Role.Id == searchedRolId)
    .Select(x => userRolesAlias.User.Id);

// here we use NHibernate built in Subqueries tools
var projection = Projections.Conditional(
    Subqueries.Exists(subquery.DetachedCriteria) // this is the SUB-SELECT
    , Projections.Constant(true)
    , Projections.Constant(false)
    );

ORIGINAL - This could look like this:

var projection = Projections.Conditional(
    NHibernate.Criterion.Expression
        .Sql("EXISTS (SELECT Id FROM TABLE2 WHERE TABLE2.ID = {alieas}.ID)")
    , Projections.Constant(true)
    , Projections.Constant(false)
    );
var query = session.QueryOver<Table1>();

ResultDto result = null;
var list = query.SelectList(l => l
    .Select(x => x.ID).WithAlias(() => result.ID)
    .Select(projection).WithAlias(() => result.DoesExist)
    )
    .TransformUsing(Transformers.AliasToBean<ResultDto>())
    .List<ResultDto>();

The list here contains the set of ResultDtos, with ID and decision if the subtabel TABLE2 record exists. The {alias} will be replaced by NHibernate with the alias of the outert table...

Where this is our DTO for projections:

public class ResultDto
{
    public virtual int ID { get; set; }
    public virtual bool DoesExist { get; set; }
}