List of Users with their single Role in MVC 6 left joined

Question

Using VS 2015, MVC 6 RC1 and EF 7 RC1 I'm trying to get a list of all users with their single role name.

I have tried all suggestions I have come across on StackOverflow but none have worked.

The SQL I'm looking for is:

SELECT [User].[Email], [Role].[Name]
FROM [User]
LEFT JOIN [UserRoles] ON [User].[Id] = [UserRoles].[UserId]
LEFT JOIN[Role] ON [UserRoles].[RoleId] = [Role].[Id]

I have written the below and tested on LinqPad (works fine) and should have worked in my project but comes up with an error:

var q1 = (from user in _context.Users
          join ur in _context.UserRoles on user.Id equals ur.UserId into grp1
          from fgrp1 in grp1.DefaultIfEmpty()
          join r in _context.Roles on fgrp1.RoleId equals r.Id into grp2
          from fgrp2 in grp2.DefaultIfEmpty()
          select new { UserEmail = user.Email, UserRoleName = fgrp2.Name }).ToList();

And the error message is:

An unhandled exception occurred while processing the request.
InvalidOperationException: Sequence contains no elements
System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)

I don't know if it makes any difference but I have created my own Role Entity as:

public class ExtranetRole : IdentityRole<int> { }

and my own User entity as:

public class ExtranetUser : IdentityUser<int> { }

Any help/suggestions much appreciated.

Answer 1

EF7 RC1 have some bugs, related to LEFT joins. Check https://github.com/aspnet/EntityFramework/issues/3629 - it looks very similar to your problem.

I think the best solution until EF release is just read all three tables separately and join them in memory using linq (to objects). Guaranteed to work :)