将 SQL 转换为 linq 语句

本文关键字：linq 语句 转换 SQL | 更新日期: 2023-09-27 17:57:02

我想将下面的SQL语法转换为linq语句以返回单个布尔结果。SQL 正在检查当前角色中是否存在用户。

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

我尝试了以下 linq 语法无济于事。知道我可以调整什么以从 case 语句中返回布尔结果吗？

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

将 SQL 转换为 linq 语句

只需使用 Any（），它将返回一个布尔值，如果您的查询返回任何内容，则返回 true，否则返回 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();

顺便说一下，也可以在 linq 中使用 join 重写您的查询，但是...这是另一个问题;)

以下内容应该使用联接（如果不是速度性能优势，则更易于维护/可读）和 Any（） linq 方法在存在一条或多条满足查询条件的记录时返回布尔值。 

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();
相关文章：