asp.net-mvc - 如何将此 SQL 转换为 Linq 语句?
问题描述
我正在开发一种向某些用户添加或删除角色的功能。将此 SQL 代码转换为 LINQ 时遇到问题。我已经测试了 SQL,它在 SQL Server Management Studio 上返回了正确的结果。
由于我是 LINQ 的初学者,我发现很难转换我的 SQL 语句。
/* This sql code works well - returns the users which DO NOT have the role yet in the given company. */
SELECT *
FROM SP.AppUser au
left join
(
SELECT distinct auc.AppUserID
FROM SP.AppUserCompany auc
inner join SP.AppUserCompanyRole aucr ON aucr.AppUserCompanyID = auc.AppUserCompanyID
where auc.CompanyID = 2 and AppRoleID = 4
) AS exist ON exist.AppUserID = au.AppUserID
Where au.AppUserID IN(2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064)
and exist.AppUserID is null
// Sample code in C# to represent the entities:
AppUser stanley = new AppUser() { AppUserID = 1, FirstName = "Stanley" };
AppUser jerson = new AppUser() { AppUserID = 2, FirstName = "Jerson" };
AppUser philip = new AppUser() { AppUserID = 3, FirstName = "Philip" };
AppUser samantha = new AppUser() { AppUserID = 4, FirstName = "Samantha" };
AppUserCompany auc1 = new AppUserCompany() { AppUserCompanyID = 1, CompanyID = 1, AppUser = stanley };
AppUserCompany auc2 = new AppUserCompany() { AppUserCompanyID = 2, CompanyID = 2, AppUser = stanley };
AppUserCompany auc3 = new AppUserCompany() { AppUserCompanyID = 3, CompanyID = 1, AppUser = jerson };
AppUserCompany auc4 = new AppUserCompany() { AppUserCompanyID = 4, CompanyID = 1, AppUser = philip };
AppUserCompany auc5 = new AppUserCompany() { AppUserCompanyID = 5, CompanyID = 2, AppUser = jerson };
AppUserCompany auc6 = new AppUserCompany() { AppUserCompanyID = 6, CompanyID = 3, AppUser = stanley };
AppUserCompanyRole aucr1 = new AppUserCompanyRole() { AppUserCompanyRoleID = 1, AppUserCompany = auc1, AppRoleID = 1 };
AppUserCompanyRole aucr2 = new AppUserCompanyRole() { AppUserCompanyRoleID = 2, AppUserCompany = auc2, AppRoleID = 1 };
AppUserCompanyRole aucr3 = new AppUserCompanyRole() { AppUserCompanyRoleID = 3, AppUserCompany = auc3, AppRoleID = 1 };
AppUserCompanyRole aucr4 = new AppUserCompanyRole() { AppUserCompanyRoleID = 4, AppUserCompany = auc4, AppRoleID = 1 };
AppUserCompanyRole aucr5 = new AppUserCompanyRole() { AppUserCompanyRoleID = 5, AppUserCompany = auc5, AppRoleID = 1 };
AppUserCompanyRole aucr6 = new AppUserCompanyRole() { AppUserCompanyRoleID = 6, AppUserCompany = auc6, AppRoleID = 1 };
List<AppUser> users = new List<AppUser> { stanley, jerson, philip, samantha };
List<AppUserCompany> appUserCompanies = new List<AppUserCompany> { auc1, auc2, auc3, auc4, auc5, auc6 };
List<AppUserCompanyRole> appUserCompanyRoles = new List<AppUserCompanyRole> { aucr1, aucr2, aucr3, aucr4, aucr5, aucr6 };
// Here is the LINQ code that I tried to get the users "who already have role" in a particular company
var ids = new List<int> { 2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064 };
var usersToDeleteRole = (from user in appUserDataBinding.Model
where ids.Contains(user.AppUserID)
join auc in appUserCompanyDataBinding.Model on user.AppUserID equals auc.AppUserID into groupJoin1
from subAuc in
(from auc in groupJoin1
join aucr in appUserCompanyRoleDataBinding.Model on auc.AppUserCompanyID equals aucr.AppUserCompanyID
where auc.CompanyID == 2 && aucr.AppRoleID == 4
select auc)
orderby user.LastName
select user).ToList();
我的预期结果将是一个 LINQ 语句,它返回在特定给定公司中还没有该角色的用户列表。我上面的 SQL 语句在 SSMS 上工作,但我无法在 C# 的 LINQ 中实现它——这意味着我需要上面的 LINQ 代码的“否定”版本。
感谢您的帮助。干杯。
解决方案
您可以对子查询使用 let 语句
public class AppUser
{
public int AppUserID { get; set; }
public string FirstName { get; set; }
}
public class AppUserCompany
{
public int AppUserCompanyID { get; set; }
public int CompanyID { get; set; }
public AppUser AppUser { get; set; }
}
public class AppUserCompanyRole
{
public int AppUserCompanyRoleID { get; set; }
public int AppRoleID { get; set; }
public AppUserCompany AppUserCompany { get; set; }
}
class Program
{
static void Main(string[] args)
{
// Sample code in C# to represent the entities:
AppUser stanley = new AppUser() { AppUserID = 1, FirstName = "Stanley" };
AppUser jerson = new AppUser() { AppUserID = 2, FirstName = "Jerson" };
AppUser philip = new AppUser() { AppUserID = 3, FirstName = "Philip" };
AppUser samantha = new AppUser() { AppUserID = 4, FirstName = "Samantha" };
AppUserCompany auc1 = new AppUserCompany() { AppUserCompanyID = 1, CompanyID = 1, AppUser = stanley };
AppUserCompany auc2 = new AppUserCompany() { AppUserCompanyID = 2, CompanyID = 2, AppUser = stanley };
AppUserCompany auc3 = new AppUserCompany() { AppUserCompanyID = 3, CompanyID = 1, AppUser = jerson };
AppUserCompany auc4 = new AppUserCompany() { AppUserCompanyID = 4, CompanyID = 1, AppUser = philip };
AppUserCompany auc5 = new AppUserCompany() { AppUserCompanyID = 5, CompanyID = 2, AppUser = jerson };
AppUserCompany auc6 = new AppUserCompany() { AppUserCompanyID = 6, CompanyID = 3, AppUser = stanley };
AppUserCompanyRole aucr1 = new AppUserCompanyRole() { AppUserCompanyRoleID = 1, AppUserCompany = auc1, AppRoleID = 1 };
AppUserCompanyRole aucr2 = new AppUserCompanyRole() { AppUserCompanyRoleID = 2, AppUserCompany = auc2, AppRoleID = 1 };
AppUserCompanyRole aucr3 = new AppUserCompanyRole() { AppUserCompanyRoleID = 3, AppUserCompany = auc3, AppRoleID = 1 };
AppUserCompanyRole aucr4 = new AppUserCompanyRole() { AppUserCompanyRoleID = 4, AppUserCompany = auc4, AppRoleID = 1 };
AppUserCompanyRole aucr5 = new AppUserCompanyRole() { AppUserCompanyRoleID = 5, AppUserCompany = auc5, AppRoleID = 1 };
AppUserCompanyRole aucr6 = new AppUserCompanyRole() { AppUserCompanyRoleID = 6, AppUserCompany = auc6, AppRoleID = 1 };
List<AppUser> users = new List<AppUser> { stanley, jerson, philip, samantha };
List<AppUserCompany> appUserCompanies = new List<AppUserCompany> { auc1, auc2, auc3, auc4, auc5, auc6 };
List<AppUserCompanyRole> appUserCompanyRoles = new List<AppUserCompanyRole> { aucr1, aucr2, aucr3, aucr4, aucr5, aucr6 };
var ids = new List<int> { 2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064 };
var result = from au in users
let appUserIDs = (from auc in appUserCompanies
join aucr in appUserCompanyRoles on auc.AppUserCompanyID equals aucr.AppUserCompany.AppUserCompanyID
where auc.CompanyID == 2 && aucr.AppRoleID == 4 && auc.AppUser.AppUserID == au.AppUserID
select auc.AppUser.AppUserID).Distinct()
where appUserIDs.Count() == 0 && ids.Contains(au.AppUserID)
select au;
推荐阅读
- ssl - 我正在 Hyperledger Fabric 网络中设置 RAFT 订购服务。我对服务器证书和服务器密钥感到困惑
- javascript - 如何更新 useState 中的变量
- flutter - 自动隐藏状态栏
- google-bigquery - 附加辅助列时的 BigQuery GROUP BY 与 PARTITION BY
- python - 从 Python 中的字符串中提取抽象名词和形容词
- amazon-web-services - 当您将 Macbook 内容迁移到新 Mac 时,您的 AWS CLI 会发生什么情况?
- python - 如何在登录时验证用户名和密码,其中现有密码由存储在数据库中的 make_password 加密?
- node.js - Mongoose runValidators 无法使用 positional$ 处理 findOneandUpdate
- java - Flink 1.4.1 向 Job Manager 发送作业时随机响应 HTTP 500
- javascript - firebase web sign up with google 只有在第二次点击后才有效?