Entity Framework Core 5.0 How To Convert Linq For Many-to-many Join To Use Intersection Table For Asp.net Membership

Question: How do I convert a LINQ query the performs a LEFT OUTER JOIN on a sub-select that INNER JOINS two tables and has a predicate? Context: I am upgrading from Entity Framewor

Solution 1:

If I were you I wrote this query like this:

var result = context.UserRoles
                    .Where(x => x.UserId == ID_TO_SEARCH)
                    .Join(
                        context.Roles,
                        ur => ur.RoleId,
                        r => r.Id,
                        (ur, role) =>new
                        {
                            ur,
                            role
                        }
                    )
                    .Select(x => x.role.Name)
                    .FirstOrDefault();

This produces query which, as for me, totally fine and more elegant:

SELECT TOP(1) [a0].[Name]
FROM [AspNetUserRoles] AS [a]
INNER JOIN [AspNetRoles] AS [a0] ON [a].[RoleId] = [a0].[Id]
WHERE [a].[UserId] = N''

UPDATE:

If I understand correctly what I've been asked in the comments, then this query will select a role name LIKE LEFT JOIN:

var rolesQuery = context.UserRoles
                         .Join(
                            context.Roles,
                            ur => ur.RoleId,
                            r => r.Id,
                            (ur, r) =>new
                            {
                                ur,
                                r
                            }
                        );
                    
    var result = context.Users
                        .Where(x => x.Id == "")
                        .Select(u =>new
                        {
                           Name = u.UserName,
                           Role = rolesQuery
                               .Where(sub=> sub.ur.UserId == u.Id)
                               .Select(sub=> sub.r.Name)
                               .FirstOrDefault()
                        })
                        .FirstOrDefault();

Which results in this SQL statement:

SELECT TOP(1) [a1].[UserName] AS [Name], (
          SELECT TOP(1) [a0].[Name]
          FROM [AspNetUserRoles] AS [a]
          INNER JOIN [AspNetRoles] AS [a0] ON [a].[RoleId] = [a0].[Id]
          WHERE [a].[UserId] = [a1].[Id]) AS [Role]
      FROM [AspNetUsers] AS [a1]
      WHERE [a1].[Id] = N''

As you can see there is NO LEFT JOIN, but sub-select will return data in similar way as LEFT JOIN does. Unfortunately, lambda-based queries do not support full LEFT JOIN and the only option to write real LEFT JOIN can be riched with SQL-like IQueryable.

I saw a method called LeftJoin() inside EF core 5 lib, but it throws NotImplementedException. I think it something that will be released later

Solution 2:

If you need just role names for particular user, query should be simplified:

var query = 
   from r inthis.DbContext.aspnet_Users
   where r.UserId == dpass.UserId
   from ro in r.aspnet_Roles
   select ro.RoleName;