c# - 手动编写 ASP.Net Identity 现有数据库脚本

Question

我有一个现有的数据库，其中已经有一个角色表，我试图让 Identity 使用表中的现有角色，但我不断收到此错误。

The entity types 'ApplicationRole' and 'Role' cannot share table 'Roles' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

我试图实现的表映射是，

• AspNetUsers -> 用户 (ApplicationUser)
• AspNetRoles -> 角色(ApplicationRole)
• AspNetUserLogins -> 用户登录(ApplicationUserLogin)
• AspNetUserRoles -> RoleUser (ApplicationUserRole)

由于数据库正在被其他应用程序使用，我无法使用代码优先。所以我必须将我的脚本更改发送给 DBA。

** 现有 SQL 表 **

/* Object:  Table [dbo].[Role] */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Role](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](100) NOT NULL CONSTRAINT [DF_Role_Description]  DEFAULT (''),
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/* Object:  Table [dbo].[RoleUser] */
CREATE TABLE [dbo].[RoleUser](
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_AppUserRole] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC,
    [RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

/* Object:  Table [dbo].[User] */
CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [UserName] [dbo].[shortString] NOT NULL CONSTRAINT [DF_User_UserName]  DEFAULT (''),
    [FirstName] [dbo].[shortString] NULL CONSTRAINT [DF_User_FirstName]  DEFAULT (''),
    [LastName] [dbo].[shortString] NULL CONSTRAINT [DF_User_LastName]  DEFAULT (''),
    [Email] [dbo].[longString] NULL CONSTRAINT [DF_User_Email]  DEFAULT (''),
    [Pager] [dbo].[smallString] NULL CONSTRAINT [DF_User_Pager]  DEFAULT (''),
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive]  DEFAULT ((1)),
    [LastPasswordChange] [datetime] NULL,
    [AccountLockedDate] [datetime] NULL,
    [AccountLockedByComputerName] [dbo].[shortString] NULL,
    [AccountLockedByUserName] [dbo].[shortString] NULL,
    [LastActive] [datetime] NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [Discriminator] [nvarchar](max) NULL,
    [EmailConfirmed] [bit] NULL,
    [PhoneNumber] [nvarchar](50) NULL,
    [PhoneNumberConfirmed] [bit] NULL,
    [TwoFactorEnabled] [bit] NULL,
    [LockoutEndDateUtc] [datetime] NULL,
    [LockoutEnabled] [bit] NULL,
    [AccessFailedCount] [int] NULL,
 CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

数据库上下文

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // Asp.net Identity
            modelBuilder.Entity<ApplicationUser>().ToTable("User");
            modelBuilder.Entity<ApplicationRole>().ToTable("Role");
            modelBuilder.Entity<ApplicationUserClaim>().ToTable("UserClaims");
            modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
            modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
        }

身份类别

public class ApplicationUserLogin : IdentityUserLogin<int> { }
    public class ApplicationUserClaim : IdentityUserClaim<int> { }
    public class ApplicationUserRole : IdentityUserRole<int> { }

    public class ApplicationRole : IdentityRole<int, ApplicationUserRole>, IRole<int>
    {
        public string Description { get; set; }

        public ApplicationRole() : base() { }
        public ApplicationRole(string name)
            : this()
        {
            this.Name = name;
        }

        public ApplicationRole(string name, string description)
            : this(name)
        {
            this.Description = description;
        }
    }

    public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUser<int>
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Password { get; set; }

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser, int> manager)
        {
            var userIdentity = await manager
                .CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            return userIdentity;
        }
    }

试图建立 FK/PK 关系但没有成功。

Answer 1

如果我创建一个新的 ASP.NET MVC 应用程序并按照您的指示更改身份内容并创建一个仅包含您指定的表的数据库，然后我尝试登录然后我收到错误

Invalid column name 'Password'.

这是有道理的，因为在您的表定义中没有“密码”字段，但该字段是在您的实体中定义的。

然后我将 OnModelCreating 方法从:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // Asp.net Identity
    modelBuilder.Entity<ApplicationUser>().ToTable("User");
    modelBuilder.Entity<ApplicationRole>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserClaim>().ToTable("UserClaims");
    modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
    modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
}

到:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // Asp.net Identity
    modelBuilder.Entity<ApplicationUser>().ToTable("User");
    modelBuilder.Entity<ApplicationRole>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserClaim>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
    modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
}

然后我得到错误:

The entity types 'ApplicationUserClaim' and ApplicationRole' cannot share table 'Role' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

注意我的错误是如何提示表 Role 而你的错误是如何提示表 Roles

所以，考虑到所有这些，我有一些观察、建议和要求:

1. 没有任何根本性问题会阻止您以您想要的方式使用 ASP.NET Identity，因为我能够根据您的需要在全新安装时进行设置，并克服了它提示实体类型共享的问题表格。
2. 也许您的应用中还有其他实体存在冲突，这可能吗？
3. 您的数据库中是否同时具有角色和角色表？还是您项目中的实体？您确定这是使用您发布的确切代码得到的错误吗？正如我所说，奇怪的是你错误地提示 Roles 而我提示 Role
4. 您能否像我所说的那样提供问题的最小可验证示例 (https://stackoverflow.com/help/mcve)，我无法从干净的 MVC 项目中重现该问题。