SQL查詢等于linq

我一直在處理這個 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 中實作這個場景。

任何幫助、建議或建議將不勝感激

uj5u.com熱心網友回復：

嘗試以下：

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

uj5u.com熱心網友回復：

嘗試以下查詢。洗掉了多余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();

uj5u.com熱心網友回復：

嘗試這個

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"

        });

標籤：C＃实体框架林克sql 到 linq 的转换

上一篇：將陣列分割成不同大小的子陣列（C#）

下一篇：選擇內的Linq變數