SQL查询等于linq

Question

我一直在处理这个SQL Server查询：

SELECT
ISNULL(x.Id, 0) Id, 
ISNULL(x.Code, 'DEFAULT') Code, 
ISNULL(x.Name, 'DEFAULT') Name 
FROM
UserApplicationAccess ua 
JOIN
MasterApplication ma ON ua.ApplicationID = ma.Id
LEFT JOIN
(SELECT
a.Id, a.Code, a.Name, m.Id AppId, u.UserCode
FROM 
ApplicationRole a 
JOIN
MasterApplication m ON a.ApplicationId = m.Id
JOIN
UserRole u ON a.Id = u.RoleId) x ON x.UserCode = ua.Usercode 
AND x.AppId = ua.ApplicationID

如何将其转换为linq？

以下是我已经尝试过的：

var application = context.MasterApplication
.Where(w => w.IsActive)
.AsNoTracking();
var access = context.UserApplicationAccess
.Where(w => w.Usercode == usercode)
.AsNoTracking();
var roles = context.ApplicationRole.AsNoTracking();
var userRole = context.UserRole
.Where(w => w.UserCode == usercode)
.AsNoTracking();
List<ApplicationRoleDTO2> UserRoles = new List<ApplicationRoleDTO2>();
UserRoles = (from a in access 
join b in application on a.ApplicationID equals b.Id 
into UserApplication 
from ua in UserApplication.Where(from ar in roles join ma in application on ar.ApplicationId equals ma.Id
join ur in userRole on ar.Id equals ur.RoleId)
).ToList();

我做了一些研究，但被linq中如何使用子查询进行左联接所困扰，当然我可以制作函数/存储过程，然后从代码中调用它，但我想知道如何在linq中实现这个场景。

如有任何帮助、建议或建议，将不胜感激

Answer 1

试试这个

var userRoles = (from r in roles
join ma in application on r.ApplicationId equals ma.Id
join ur in userRole on r.Id equals ur.RoleId
select new { r.Id, r.Code, r.Name, AppId = ma.Id, ur.UserCode });
var data = (from a in access
join ma in application on a.ApplicationId equals ma.Id
join ur in userRoles on new {a.UserCode,a.ApplicationId} equals 
new {ur.UserCode, ApplicationId = ur.AppId } into left
from x in left.DefaultIfEmpty()
select new
{
Id = (int?)x.Id ?? 0,
Code = x.Code??"DEFAULT",
Name = x.Name?? "DEFAULT"
});

Answer 2

尝试以下操作：

UserRoles = (from a in access 
join b in application on a.ApplicationID equals b.Id 
join ar in roles on ar.ApplicationId equals b.Id
join ur in userRole on ar.Id equals ur.RoleId
select new {access = a, application = b, roles = ar, userRole = ur}
).ToList();

Answer 3

尝试以下查询。删除了多余的AsNoTrackig()，它是不需要的，EF Core不跟踪自定义实体。

var application = context.MasterApplication
.Where(w => w.IsActive);
var access = context.UserApplicationAccess
.Where(w => w.Usercode == usercode);
var roles = context.ApplicationRole;
var userRole = context.UserRole
.Where(w => w.UserCode == usercode);
var rolesQuery = 
from a in roles
join m in application on a.ApplicationId equals m.Id
join u in userRole on a.Id equals u.RoleId
select new 
{
a.Id, a.Code, a.Name, AppId = m.Id, u.UserCode
};
var userRolesQuery =
from ua in access
join ma in application on ua.ApplicationID equals ma.Id
from x in rolesQuery.Where(x.UserCode == ua.Usercode && x.AppId == ua.ApplicationID)
.DefaultIfEmpty()
select new 
{
Id = (int?)x.Id ?? 0,
Code = x.Code ?? 'DEFAULT',
Name = x.Name ?? 'DEFAULT',
};
var UserRoles = userRolesQuery.ToList();