title: Left join after a into group in Linq using entity framework (core)
body: <p>Problem: I would like to generate the exact sql below in the desired output using linq syntax (Entity framework 7)</p>
  
  <p><strong><em>The goal of the question is to generate the exact sql below!</em></strong>
  <strong>Desired Output</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>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></pre>
  
  <h1>Code with inner join works but I want left joins....: </h1>
  
  <pre><code> 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></pre>
  
  <h1> Code with Left Join...which I am missing some concept doesnt work</h1> 
  
  <p> 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.</p>
  
  <pre><code>        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();
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/9zydL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9zydL.png" alt="enter image description here"></a></p>