实体框架核心错误地查询关系 [英] Entity Framework Core incorrectly querying relationships
问题描述
我有一个使用Identity 2.0的现有ASP.NET MVC应用程序。我正在尝试使用带有Ef Core 2.1的新Core 2.1应用查询用户对象。
I have an existing ASP.NET MVC app that uses Identity 2.0. I'm trying to query the user objects using a new Core 2.1 app with Ef Core 2.1.
我直接查询而不是使用UserManager / RoleManager,因为.NET MVC和.NET核心应用程序的版本不同,所以我不想陷入困境麻烦。
I'm querying directly rather than using UserManager / RoleManager as the .NET MVC and .NET core apps have different versions and I don't want to get myself into any trouble down the track.
我的问题是我无法让所有用户都扮演特定角色。
My problem is that I cannot get all the users in a particular role.
我正在尝试在我的.net核心应用程序中这样做:
I am trying to do itvlike this in my .net core application:
public partial class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{ }
public virtual DbSet<ApplicationUser> AspNetUsers { get; set; }
public virtual DbSet<AspNetRole> AspNetRoles { get; set; }
public virtual DbSet<AspNetUserRole> AspNetUserRoles { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<AspNetUserRole>()
.HasKey(pc => new { pc.UserId, pc.RoleId });
}
}
我的角色映射模型:
public class AspNetRole
{
[Key]
public Guid Id { get; set; }
[MaxLength(256)]
[Required]
public string Name {get; set;}
public virtual ICollection<AspNetUserRole> AspNetUserRoles {get; set;}
}
我的模型来映射用户:
public class ApplicationUser : IdentityUser
{
public virtual ICollection<AspNetUserRole> AspNetUserRoles {get; set;}
}
和我的联接表:
public class AspNetUserRole
{
[MaxLength(256)]
[Required]
public Guid UserId { get; set; }
public ApplicationUser User {get; set;}
[MaxLength(256)]
[Required]
public Guid RoleId { get; set; }
public AspNetRole Role {get; set;}
}
我在存储库中运行的查询是这样的:
The query that I am running in my repository is this:
var usersInRole = _context.AspNetRoles
.Where(p => p.Name == "Manager")
.SelectMany(p => p.AspNetUserRoles)
.Select(pc => pc.User);
但是查询失败。 EF正在翻译,如下所示(我从SELECT语句中删除了一堆字段):
However the query is failing. EF is translating is as follows (I've taken out a bunch of fields from the SELECT statement):
SELECT [p.AspNetUserRoles.User]。[Id], [p.AspNetUserRoles.User]。[用户名]
来自[AspNetRoles] AS [p]
内连接[AspNetUserRoles] AS [p.AspNetUserRoles] ON [p]。[Id] = [p。 AspNetUserRoles]。[角色ID]
左联接[AspNetUsers] AS [p.AspNetUserRoles.User]开启[p.AspNetUserRoles]。[UserId1] = [p.AspNetUserRoles.User]。[Id]
[p]。[名称] = @__ role_0
SELECT [p.AspNetUserRoles.User].[Id], [p.AspNetUserRoles.User].[UserName] FROM [AspNetRoles] AS [p] INNER JOIN [AspNetUserRoles] AS [p.AspNetUserRoles] ON [p].[Id] = [p.AspNetUserRoles].[RoleId] LEFT JOIN [AspNetUsers] AS [p.AspNetUserRoles.User] ON [p.AspNetUserRoles].[UserId1] = [p.AspNetUserRoles.User].[Id] WHERE [p].[Name] = @__role_0
如您所见,它错误地查询了[p.AspNetUserRoles]。[UserId1]因此出现以下错误:
As you can see it's incorrectly querying [p.AspNetUserRoles].[UserId1] and therefore gives the following error:
System.Data.SqlClient.SqlException(0x80131904):无效的列名'UserId1'。
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UserId1'.
推荐答案
除了ApplicationDbContext类的 OnModelCreating
方法中的代码外,您还需要添加以下代码
You need to add following code in addition to your code in OnModelCreating
method of ApplicationDbContext class
modelBuilder.Entity<AspNetUserRole>()
.HasOne(aur => aur.User)
.WithMany(aur => aur.AspNetUserRoles)
.HasForeignKey(aur => aur.UserId);
modelBuilder.Entity<AspNetUserRole>()
.HasOne(aur => aur.Role)
.WithMany(aur => aur.AspNetUserRoles)
.HasForeignKey(aur => aur.RoleId);
这篇关于实体框架核心错误地查询关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!