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

Question

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

The purpose of the question is to create the exact sql below! Desired Result

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 can only get the same sql, but with internal connections. I can't seem to get two left connections. Below is the code below, how I was able to generate internal joins, as well as an attempt to crash when creating 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] 

The code with the inner join works, but I need 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(); 

The code with the left connection ... which I do not have, does not work

what doesn't work is that on g2.RoleId is equal to d.Id in the group3 g2 line is not available. So how can I make c.RoleId available for my next left connection? Basically, after you group something, you can no longer use it.

  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

If someone comes to this question thinking that they haven’t answered him, the answer is currently buried in the comment chain after the question. I am simply retelling the key comments here.

The problem is that Entity Framework 7 is currently a release candidate and has some bugs. One of these errors ( Left Join does not work if the filter is composed on top ) causes the left join to fail, marked by the OP.

The solution now is to return to Entity Framework 6 or temporarily use a stored procedure or embedded SQL until the error is fixed.

Answer 2

If you understood correctly that the problem is that EF does not create a LEft union. If yes, then the solution is quite simple, your objects should have a nullable property, for example

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

One of the options that is in my head

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

Another 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 the problem, as we say in the comments that IdentityUserRole is not entitled to the role, therefore, it allows us to fix this. create class

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

Then extend the 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)) 

Answer 3

This is a job that generates data with a very bad query. It generates a series of calls to the database that give the same set of results. However, this is definitely not the best request for the job. I'm still waiting for RC2 and I will update the answer.

  var query = ( //INNER JOIN from a in _dbCtx.AppUser join b in _dbCtx.Users on a.AspNetUserId equals b.Id from c in _dbCtx.UserRoles .Where(x => b!=null && x.UserId == b.Id) .DefaultIfEmpty() from d in _dbCtx.Roles .Where(x => a !=null && x.Id == a.RoleId) .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 = d.Name != null ? string.Empty :d.Name , SupervisorId = a.SupervisorId, BackUpId = a.BackUpId, HasAutoAssignClaims = a.HasAutoAssignClaims, IsActive = a.IsActive }).ToList();