Convert SQL to linq statement

Question

I would like to convert the SQL syntax below to a linq statement to get a single boolean result back. The SQL is checking if a user exists in a current role.

select case
     when exists (select 1
                   from Global.Application as a
                   join Global.Role as r on a.ID = r.ApplicationId and r.Name = 'GlobalAdmin'
                   join Global.[Authorization] as au on a.ID = au.ApplicationId and r.ID = au.RoleId
                   join Global.[User] as u on au.UserId = u.ID and u.UserPrincipalName = 'domain\username'
                  where a.EnableApplication = 1 and EnableAuthorization = 1 and EnableRoles = 1 and a.ID = 1)
     then CAST(1 as BIT)
     else CAST(0 as BIT)
   end as UserExists

I've tried the following linq syntax to no avail. Any idea what I can tweak to get the boolean result back from the case statement?

var result = (from a in _applicationRepository.GetList(a => a.ID == applicationId)
                      from r in _roleRepository.GetList(r => r.ApplicationId == a.ID && r.Name == rolename)
                      from au in _authorizationRepository.GetList(au => au.ApplicationId == a.ID && r.ID == au.RoleId)
                      from u in _userRepository.GetList(u => u.ID == au.UserId && u.UserPrincipalName == username)
                      where a.EnableApplication == true && a.EnableAuthorization == true && a.EnableRoles == true && a.ID == applicationId
                      select (new bool{UserExists = 1 })).Single();

Answer 1

The following should work using joins (much more maintainable / readable if not a speed performance benefit) and the Any() linq method to return a bool if one or more records exists that meet the query criteria.

bool exists = (from a in Global.Application
                join Global.Role as r on a.ID equals r.ApplicationId
                join Global.[Authorization] as auth on a.ID equals auth.ApplicationId
                join Global.[User] as user on auth.UserId equals user.ID
                where a.EnableApplication == 1
                && a.EnableAuthorization == 1
                && a.EnableRoles == 1
                && a.ID == 1
                && r.Name == "GlobalAdmin"
                && r.ID == auth.RoleId
                && user.UserPrincipalName == "domain\username"
                select a.ID).Any();

Answer 2

Just use Any(), which will return a boolean, true if your query returns anything, else false.

var result =(from a in _applicationRepository.GetList(a => a.ID == applicationId)
                      from r in _roleRepository.GetList(r => r.ApplicationId == a.ID && r.Name == rolename)
                      from au in _authorizationRepository.GetList(au => au.ApplicationId == a.ID && r.ID == au.RoleId)
                      from u in _userRepository.GetList(u => u.ID == au.UserId && u.UserPrincipalName == username)
                      where a.EnableApplication == true && a.EnableAuthorization == true && a.EnableRoles == true && a.ID == applicationId
                      //select 1, or a, or anything, it doesn't really mind
                      select 1).Any();

By the way, your query could be rewritten using join in linq also, but... that's another problem ;)