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 Framework 6 (EF6) to Entity Framework Core 5 (EFCore) using the EFCore Tools Reverse Engineering feature. I have a query that is using LINQ to query a many-to-many relationship on the ASP.NET Membership system between the AspNet_Users and AspNet_Roles tables. The query abstracts away the join through the AspNet_UsersInRoles intersection table. As a result, in EF6 I have the following LINQ:
(from r in this.DbContext.aspnet_Users
where r.UserId == dpass.UserId
select r.aspnet_Roles.Select(x => x.RoleName)
).FirstOrDefault();
That generates the following SQL Query (retrieved using SQL Server Profiler):
DECLARE @p__linq__0 uniqueidentifier = '<Runtime_GUID>'
SELECT
[Limit1].[UserId] AS [UserId],
[Join1].[RoleName] AS [RoleName],
CASE WHEN ([Join1].[UserId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (1) [Extent1].[UserId] AS [UserId]
FROM [dbo].[aspnet_Users] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0
) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent2].[UserId] AS [UserId]
, [Extent3].[RoleName] AS [RoleName]
FROM [dbo].[aspnet_UsersInRoles] AS [Extent2]
INNER JOIN [dbo].[aspnet_Roles] AS [Extent3]
ON [Extent3].[RoleId] = [Extent2].[RoleId]
) AS [Join1]
ON [Limit1].[UserId] = [Join1].[UserId]
I have converted the LINQ to the following:
(from ur in this.DbContext.aspnet_UsersInRoles
join r in this.DbContext.aspnet_Roles
on ur.RoleId equals r.RoleId
where ur.UserId == dpass.UserId
join u in this.DbContext.aspnet_Users
on ur.UserId equals u.UserId
into UserRolesJoined from UserRoles in UserRolesJoined.DefaultIfEmpty()
select new { ur.UserId, ur.Role.RoleName }
)
That generates the following SQL query (retrieved using EFCore's new .ToQueryString() method):
DECLARE @__dpass_UserId_1 uniqueIdentifier = '<Runtime_GUID>'
SELECT [a].[UserId], [a2].[RoleName]
FROM [aspnet_UsersInRoles] AS [a]
INNER JOIN [aspnet_Roles] AS [a0] ON [a].[RoleId] = [a0].[RoleId]
LEFT JOIN [aspnet_Users] AS [a1] ON [a].[UserId] = [a1].[UserId]
INNER JOIN [aspnet_Roles] AS [a2] ON [a].[RoleId] = [a2].[RoleId]
WHERE [a].[UserId] = @__dpass_UserId_1
The SQL SELECT statement from EFCore is different than the SQL SELECT statement from EF6; however, the SQL Select result is identical. Is this the correct way to re-write the LINQ, or did I get it wrong? Any help is greatly appreciated.
UPDATE
Once I ran the Reverse Engineering tool, the original LINQ command had the following errors:
CS1061 'aspnet_Users' does not contain a definition for 'aspnet_Roles' and no accessible extension method 'aspnet_Roles' accepting a first argument of type 'aspnet_Users' could be found (are you missing a using directive or an assembly reference?)When I add public IEnumerable<object> aspnet_Roles; to aspnet_users, I then get the following error.
CS1061 'object' does not contain a definition for 'RoleName' and no accessible extension method 'RoleName' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)Below are the tables in question from the ASP.NET Membership system that Microsoft created. For reference for the full schema, see: https://learn.microsoft.com/en-us/aspnet/web-forms/overview/older-versions-security/membership/creating-the-membership-schema-in-sql-server-cs
aspnet_UserInRoles
[Index(nameof(RoleId), Name = "aspnet_UsersInRoles_index")]
public partial class aspnet_UsersInRoles
{
[Key]
public Guid UserId { get; set; }
[Key]
public Guid RoleId { get; set; }
[ForeignKey(nameof(RoleId))]
[InverseProperty(nameof(aspnet_Roles.aspnet_UsersInRoles))]
public virtual aspnet_Roles Role { get; set; }
[ForeignKey(nameof(UserId))]
[InverseProperty(nameof(aspnet_Users.aspnet_UsersInRoles))]
public virtual aspnet_Users User { get; set; }
}
aspnet_Roles
public partial class aspnet_Roles
{
public aspnet_Roles()
{
aspnet_UsersInRoles = new HashSet<aspnet_UsersInRoles>();
}
public Guid ApplicationId { get; set; }
[Key]
public Guid RoleId { get; set; }
[Required]
[StringLength(256)]
public string RoleName { get; set; }
[Required]
[StringLength(256)]
public string LoweredRoleName { get; set; }
[StringLength(256)]
public string Description { get; set; }
[ForeignKey(nameof(ApplicationId))]
[InverseProperty(nameof(aspnet_Applications.aspnet_Roles))]
public virtual aspnet_Applications Application { get; set; }
[InverseProperty("Role")]
public virtual ICollection<aspnet_UsersInRoles> aspnet_UsersInRoles { get; set; }
}
aspnet_Users
[Index(nameof(ApplicationId), nameof(LastActivityDate), Name = "aspnet_Users_Index2")]
public partial class aspnet_Users
{
public aspnet_Users()
{
Users = new HashSet<Users>();
aspnet_PersonalizationPerUser = new HashSet<aspnet_PersonalizationPerUser>();
aspnet_UsersInRoles = new HashSet<aspnet_UsersInRoles>();
}
public Guid ApplicationId { get; set; }
[Key]
public Guid UserId { get; set; }
[Required]
[StringLength(256)]
public string UserName { get; set; }
[Required]
[StringLength(256)]
public string LoweredUserName { get; set; }
[StringLength(16)]
public string MobileAlias { get; set; }
public bool IsAnonymous { get; set; }
[Column(TypeName = "datetime")]
public DateTime LastActivityDate { get; set; }
[ForeignKey(nameof(ApplicationId))]
[InverseProperty(nameof(aspnet_Applications.aspnet_Users))]
public virtual aspnet_Applications Application { get; set; }
[InverseProperty("User")]
public virtual aspnet_Membership aspnet_Membership { get; set; }
[InverseProperty("User")]
public virtual aspnet_Profile aspnet_Profile { get; set; }
[InverseProperty("aspUser")]
public virtual ICollection<Users> Users { get; set; }
[InverseProperty("User")]
public virtual ICollection<aspnet_PersonalizationPerUser> aspnet_PersonalizationPerUser { get; set; }
[InverseProperty("User")]
public virtual ICollection<aspnet_UsersInRoles> aspnet_UsersInRoles { get; set; }
}
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