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
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"
});