Left join after a into group in Linq using entity framework (core)

Question

Problem: I would like to generate the exact sql below in the desired output using linq syntax (Entity framework 7)

The goal of the question is to generate the exact sql below! Desired Output

select a.AppUserId, u.Email, a.FirstName, a.MiddleName, a.LastName, a.IsInternal, a.AspNetUserId, a.PictureLink, a.SignatureLink, a.PhoneNumber, a.Extension, a.FaxNumber, a.MobileNumber, a.Skype, r.Name as 'Role', a.SupervisorId, a.BackUpId, a.HasAutoAssignClaims, a.IsActive
from AppUser a
join AspNetUsers u on a.AspNetUserId = u.Id
left join AspNetUserRoles ur on u.Id = ur.UserId
left join AspNetRoles r on ur.RoleId = r.Id

I have only being able to get the exact same sql but with inner joins. I can't seem to get the two left joins. The code below here how I was able to generate the inner joins and also a fail attempt at generating the left joins.

SELECT [a].[AppUserId], [a].[FirstName], [a].[MiddleName], [a].[LastName], [a].[IsInternal], [a].[AspNetUserId], [a].[PictureLink], [a].[SignatureLink], [a].[PhoneNumber], [a].[Extension], [a].[FaxNumber], [a].[MobileNumber], [a].[Skype], [a].[SupervisorId], [a].[BackUpId], [a].[HasAutoAssignClaims], [a].[IsActive]
FROM [AppUser] AS [a]
INNER JOIN [AspNetUsers] AS [b] ON [a].[AspNetUserId] = [b].[Id]
INNER JOIN [AspNetUserRoles] AS [c] ON [b].[Id] = [c].[UserId]
INNER JOIN [AspNetRoles] AS [d] ON [a].[RoleId] = [d].[Id]

Code with inner join works but I want left joins....:

 var query = (
                              //INNER JOIN 
                              from a in _dbCtx.AppUser
                              join b in _dbCtx.Users
                              on a.AspNetUserId equals b.Id 


                              ////LEFT JOIN
                              join c in _dbCtx.UserRoles
                              on b.Id equals c.UserId 

                              //    //    //LEFT JOIN  (if you wanted right join the easiest way is to flip the order of the tables. 
                              join d in _dbCtx.Roles
                              on a.RoleId equals d.Id





                          select new
                          {
                              AppUserId = a.AppUserId,
                              //Email = b.Email,
                              FirstName = a.FirstName,
                              MiddleName = a.MiddleName,
                              LastName = a.LastName,
                              IsInternal = a.IsInternal,
                              AspNetUserId = a.AspNetUserId,
                              PictureLink = a.PictureLink,
                              SignatureLink = a.SignatureLink,
                              PhoneNumber = a.PhoneNumber,
                              Extension = a.Extension,
                              FaxNumber = a.FaxNumber,
                              MobileNumber = a.MobileNumber,
                              Skype = a.Skype,
                              //Role = d.Name != null ? string.Empty :d.Name ,
                              SupervisorId = a.SupervisorId,
                              BackUpId = a.BackUpId,
                              HasAutoAssignClaims = a.HasAutoAssignClaims,
                              IsActive = a.IsActive

                          }).ToList();

Code with Left Join...which I am missing some concept doesnt work

what doesnt work is that on g2.RoleId equals d.Id into group3 line the g2 is not available. So how would I make c.RoleId available for my next left join? Basically after you group something you can no longer use it apparently.

        var LeftJoin= (
                          //INNER JOIN 
                          from a in _dbCtx.AppUser
                          join b in _dbCtx.Users
                          on a.AspNetUserId equals b.Id 


                          ////LEFT JOIN
                          join c in _dbCtx.UserRoles
                          on b.Id equals c.UserId into group2
                          from g2 in group2.DefaultIfEmpty() //makes it left join 



                          join d in _dbCtx.Roles
                          on g2.RoleId equals d.Id into group3
                          from g3 in group3.DefaultIfEmpty()

                          select new
                          {
                              AppUserId = a.AppUserId,
                              Email = b.Email,
                              FirstName = a.FirstName,
                              MiddleName = a.MiddleName,
                              LastName = a.LastName,
                              IsInternal = a.IsInternal,
                              AspNetUserId = a.AspNetUserId,
                              PictureLink = a.PictureLink,
                              SignatureLink = a.SignatureLink,
                              PhoneNumber = a.PhoneNumber,
                              Extension = a.Extension,
                              FaxNumber = a.FaxNumber,
                              MobileNumber = a.MobileNumber,
                              Skype = a.Skype,
                              Role = g3.Name != null ? string.Empty :g3.Name ,
                              SupervisorId = a.SupervisorId,
                              BackUpId = a.BackUpId,
                              HasAutoAssignClaims = a.HasAutoAssignClaims,
                              IsActive = a.IsActive

                          }).ToList();

Answer 1

In case anyone comes to this question, thinking it hasn't been answered, the answer is currently buried in a comment chain after the question. I'm merely paraphrasing the key comments here.

The problem is that Entity Framework 7 is currently a release candidate and has some bugs. One of these bugs (Left Join doesn't work if the filter is composed on top) is causing the failure of the left join noted by the OP.

The solution, for now, is to revert to Entity Framework 6, or temporarily use a stored procedure or inline SQL until the bug is fixed.

Answer 2

If i understood you properly you problem is that EF is not generating LEft join. If yes then solution is pretty simple your Entities should have nullable property for instance

 public class SomeClass
    {
        public int Id { get; set; }
        public int? CategoryId { get; set; }
        public Category Category {get;set:}
    }

One option which is in my head

_dbCtx.SqlQuery<T>(SqlStringHEre).ToList()

Other option, and aspnet tables i would do it differently

var query = _dbCtx.AppUser
.Include(apu=>apu.AspNetUser)
.Include(apu=>apu.AspNetUser.Roles)
.Include(apu=>apu.AspNetUser.Roles.Select(r=>r.Role))
.ToList();

but here is problem as we talk in comments that IdentityUserRole does not have refference to role so lets fix that. create class

 public class UserToRole : IdentityUserRole<int>
    {
        public Role Role { get; set; }
    }

Then extend your user class

 public class YourUser : IdentityUser<int, IdentityUserLogin<int>, UserToRole, IdentityUserClaim<int>>

Now you can do what you want

_db.Users.Select(u=>u.Roles.Select(r=>r.Role.Name))