如果您有一个现有数据库,并且想在其中包含ASP.NET Identity表,则可能会遇到此错误。在启动时,您可能不知道如何将 [AspNetRoles],[AspNetUserClaims],[AspNetUsers],[AspNetUserLogins] 表集成到现有数据库中。尽管有关此主题的资源很多,但此答案要简短而切合实际。您可能希望将Entity Framework中的“数据库优先”方法与ASP.NET MVC的ASP.NET Identity功能一起使用。这是一个非常简短的傻瓜教程。如果我的英语不好,我很抱歉。

最佳答案

这是Asp.NET身份表与现有数据库的最短集成。

1)在Visual Studio(2015或2013)中打开新项目或您现有的项目。打开服务器资源管理器并打开 DefaultConnection 。查找标识表。 (在WebConfig文件中,localDB连接字符串应处于事件状态。其他现有数据库的连接字符串不应处于事件状态。)双击 [AspNetRoles],[AspNetUserClaims],[AspNetUsers],[AspNetUserLogins] 表。并复制其所有SQL代码。

2)在SQL Server Management Studio中打开您现有的数据库,右键单击您的数据库,然后单击新建查询,跳过此处您在第一部分中复制的内容。您将过去类似的事情:

CREATE TABLE [dbo].[AspNetRoles] (
[Id]   NVARCHAR (128) NOT NULL,
[Name] NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
ON [dbo].[AspNetRoles]([Name] ASC);



CREATE TABLE [dbo].[AspNetUsers] (
[Id]                   NVARCHAR (128) NOT NULL,
[Email]                NVARCHAR (256) NULL,
[EmailConfirmed]       BIT            NOT NULL,
[PasswordHash]         NVARCHAR (MAX) NULL,
[SecurityStamp]        NVARCHAR (MAX) NULL,
[PhoneNumber]          NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed] BIT            NOT NULL,
[TwoFactorEnabled]     BIT            NOT NULL,
[LockoutEndDateUtc]    DATETIME       NULL,
[LockoutEnabled]       BIT            NOT NULL,
[AccessFailedCount]    INT            NOT NULL,
[UserName]             NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [dbo].[AspNetUsers]([UserName] ASC);


CREATE TABLE [dbo].[AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
 );


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
ON [dbo].[AspNetUserRoles]([UserId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
ON [dbo].[AspNetUserRoles]([RoleId] ASC);



CREATE TABLE [dbo].[AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey]   NVARCHAR (128) NOT NULL,
[UserId]        NVARCHAR (128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC),
CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
ON [dbo].[AspNetUserLogins]([UserId] ASC);



CREATE TABLE [dbo].[AspNetUserClaims] (
[Id]         INT            IDENTITY (1, 1) NOT NULL,
[UserId]     NVARCHAR (128) NOT NULL,
[ClaimType]  NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
ON [dbo].[AspNetUserClaims]([UserId] ASC);

如果您忘记了以GO开头的行,那么您将确切地看到此问题的标题中的错误。运行此查询,并等待成功创建表。现在,您现有的数据库已准备就绪,可以使用Asp.NET MVC 5的身份功能。

3)在Visual Studio中打开WebConfig。我们将在这里更改连接字符串。这样写:
 <add name="DefaultConnection" connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Persist Security Info=True;User ID=YOUR_USER_ID;Password=YOUR_PASSWORD.;MultipleActiveResultSets=True;Application Name=EntityFramework"  providerName="System.Data.SqlClient"/>

而不是localDB连接字符串。这是什么
 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-....mdf;Initial Catalog=aspnet-...;Integrated Security=True" providerName="System.Data.SqlClient" />

这就是您需要做的。运行您的项目并注册。您可以在现有数据库的AspNetUsers表中查看新的用户数据。

09-10 21:01