SQL query equals with linq

Question

I have been working on this SQL Server query:

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

How to convert this to linq?

Here's what I have already tried:

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

I've done some research but got stuck by how left join with subquery work in linq, of course I can make function/stored procedure and then call it from code, but I want to know how to implement this scenario in linq.

Any help, advice or suggestion would be really appreciated

Answer 1

Try this

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

Try the following query. Removed superfluous AsNoTrackig(), it is not needed, EF Core do not track custom entities.

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

Answer 3

Try following :

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