使用SQL脚本创建ASP.NET身份表 [英] Create ASP.NET Identity tables using SQL script
本文介绍了使用SQL脚本创建ASP.NET身份表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试将ASP.NET Identity加入到当前使用SQL脚本创建数据库模式的新应用程序中。因为我们需要从其他表创建外键约束到用户表，所以非常希望ASP.NET身份表也是在相同的脚本中创建的。
我已经能够在IdentityModels.cs中创建的ApplicationUser类中扩展IdentityUser类 -
public class ApplicationUser：IdentityUser
{
public ApplicationUser（）
{
Sequence = 0;
LastActivity = DateTime.Now;
}
[DatabaseGenerated（DatabaseGeneratedOption.Identity）]
public int NumericId
{
get;
设置;
}
[MaxLength（50），必需]
public string DisplayName
{
get;
设置;
}
[MaxLength（50），必需]
public string描述
{
get;
设置;
}
[IntegerValidator（MinValue = 0），必需]
public int Sequence
{
get;
设置;
}
[MaxLength（50）]
public string ExternalRef
{
get;
设置;
}
public DateTime？ LoggedOn
{
get;
设置;
}
public DateTime？ LoggedOff
{
get;
设置;
}
public DateTime LastActivity
{
get;
设置;
}
public int FailedLoginAttempts
{
get;
设置;
}
public DateTime？ LockedOutUntil
{
get;
设置;
}
public int LockOutCycles
{
get;
设置;
}
public bool已批准
{
get;
设置;
}
}
我已经使用脚本创建了表 - p>
CREATE TABLE [Users]。[User]（
[Id] [nvarchar]（128）NOT NULL
，[NumericId] [int] IDENTITY（1,1）NOT NULL
，[UserName] [nvarchar]（50）NULL
，[PasswordHash] [nvarchar]（max）NULL
，[SecurityStamp] [nvarchar]（max）NULL
，[DisplayName] [nvarchar]（50）NULL
，[描述] [nvarchar]（50）NOT NULL
，[EmailAddress] [nvarchar]（254）NOT NULL
，[已确认] [位] NOT NULL
，[序列] [int] NOT NULL
，[ExternalRef] [nvarchar]（50）NOT NULL
，[LoggedOn] [datetime] NULL
，[LoggedOff] [datetime] NULL
，[LastActivity] [datetime] NULL
，[FailedLoginAttempts] [int] NOT NULL
，[LockedOutUntil] [datetime] NULL
，[LockOutCycles] int NOT NULL
，[已批准] [位] NOT NULL
，[Discriminator] [nvarchar]（128）NOT NULL
，CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED（[Id] ASC）WITH（
PAD_INDEX = OFF
，STATISTICS_NORECOMPUTE = OFF
，IGNORE_DUP_KEY = OFF
，ALLOW_ROW_LOCKS = ON
，ALLOW_PAGE_LOCKS = ON
）ON [PRIMARY]
，CONSTRAINT [IX_User_NumericId] UNIQUE CLUSTERED NumericId] ASC）WITH（
PAD_INDEX = OFF
，STATISTICS_NORECOMPUTE = OFF
，IGNORE_DUP_KEY = OFF
，ALLOW_ROW_LOCKS = ON
，ALLOW_PAGE_LOCKS = ON
） ON [PRIMARY]
，CONSTRAINT [IX_User_Name] UNIQUE NONCLUSTERED（[UserName] 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
ALTER TABLE [Users]。[用户] ADD CONSTRAINT [DF_User_Description] DEFAULT（''）
FOR [描述]
GO
ALTER TABLE [Users]。[User] ADD CONSTRAINT [DF_User_Sequence] DEFAULT（（ 0））
FOR [序列]
GO
ALTER TABLE [Users]。[User] ADD CONSTRAINT [DF_User_External参考] DEFAULT（''）
FOR [ExternalRef]
GO
ALTER TABLE [Users]。[User] ADD CONSTRAINT [DF_User_FailedLoginAttempts] DEFAULT（（0））
FOR [FailedLoginAttempts]
GO
ALTER TABLE [Users]。[User] ADD CONSTRAINT [DF_User_LockOutCycles] DEFAULT（（0））
FOR [LockOutCycles]
GO
CREATE NONCLUSTERED INDEX [IX_User_Sequence] ON [Users]。[User]（[Sequence] ASC，[UserName] ASC）
WITH（
PAD_INDEX = OFF
，STATISTICS_NORECOMPUTE = OFF
，SORT_IN_TEMPDB = OFF
，IGNORE_DUP_KEY = OFF
，DROP_EXISTING = OFF
，ONLINE = OFF
，ALLOW_ROW_LOCKS = ON
，ALLOW_PAGE_LOCKS = ON
）ON [PRIMARY]
GO
CREATE TABLE [Users]。[UserClaim]（
[Id] [int] IDENTITY（1， 1）NOT NULL，
[ClaimType] [nvarchar]（max）NULL，
[ClaimValue] [nvarchar]（max）NULL，
[UserId] [nvarchar] ，
CONSTRAINT [PK_Users.UserClaims] 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] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Users]。[UserClaim] WITH CHECK ADD CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id] FOREIGN KEY（[UserId]）
参考[Users]。[User]（[Id]）
ON DELETE CASCADE
GO
ALTER TABLE [Users]。[UserClaim] CHECK CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id]
GO
CREATE TABLE [用户] [UserLogin]（
[UserId] [nvarchar]（128）NOT NULL，
[LoginProvider] [nvarchar]（128）NOT NULL，
[ProviderKey] [nvarchar] 128）NOT NULL，
CONSTRAINT [PK_Users.UserLogins] PRIMARY KEY CLUSTERED
（
[UserId] ASC，
[LoginProvider] ASC，
[ProviderKey] 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
ALTER TABLE [Users]。[UserLogin] WITH CHECK ADD CONSTRAINT [FK_Users.UserLogins_Users.User_UserId] FOREIGN KEY（[UserId]）
参考[用户] [User]（[Id]）
ON DELETE CASCADE
GO
ALTER TABLE [Users]。[UserLogin] CHECK CONSTRAINT [FK_Users.UserLogins_Users.User_UserId]
CREATE TABLE [Users]。[ApplicationRole]（
[Id] [nvarchar]（128）NOT NULL，
[Name] [nvarchar]（max）NOT NULL，
CONSTRAINT [PK_Users.ApplicationRole] 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] TEXTIMAGE_ON [PRIMARY]
C REATE TABLE [Users]。[UserRole]（
[UserId] [nvarchar]（128）NOT NULL，
[RoleId] [nvarchar]（128）NOT NULL，
CONSTRAINT [PK_Users。 UserRole] 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）ON [PRIMARY]
）ON [PRIMARY]
GO
ALTER TABLE [Users]。[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId] FOREIGN KEY（[RoleId]）
参考[Users]。[ApplicationRole]（[Id]）
ON DELETE CASCADE
GO
ALTER TABLE [Users]。[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId]
GO
ALTER TABLE [Users] 。[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.User_UserId] FOREIGN KEY（[UserId]）
参考[Users]。[User]（[Id]）
ON DELETE CASCADE
GO
ALTER TABLE [Users]。[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.User_UserId]
GO
CREATE TABLE [Users]。[Department]（
[Id] [int] IDENTITY（1，1）NOT NULL
，[名称] [nvarchar]（50）NOT NULL
，[描述] [nvarchar]（50）NOT NULL
，[序列] [int] NOT NULL
CONSTRAINT [PK_Users.Department] 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
并且使用OnModelCreating事件上的覆盖将实体映射到表 -
public class ApplicationDbContext：IdentityDbContext< IdentityUser>
{
public ApplicationDbContext（）
：base（DefaultConnection）
{
}
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Entity< IdentityUser>（）。ToTable（User，Users）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.Id）.HasColumnName（Id）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.UserName）.HasColumnName（UserName）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.Email）.HasColumnName（EmailAddress）。HasMaxLength（254）.IsRequired（）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.PasswordHash）.HasColumnName（PasswordHash）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.SecurityStamp）.HasColumnName（SecurityStamp）;
modelBuilder.Entity< IdentityUser>（）。Property（iu => iu.IsConfirmed）.HasColumnName（已确认）;
modelBuilder.Entity< ApplicationUser>（）。HasKey（au => au.Id）.ToTable（User，Users）; //指定我们自己的表名，而不是默认值
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.Id）.HasColumnName（Id）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.NumericId）.HasColumnName（NumericId）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.UserName）.HasMaxLength（50）.HasColumnName（UserName）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.PasswordHash）.HasColumnName（PasswordHash）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.SecurityStamp）.HasColumnName（SecurityStamp）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.DisplayName）.HasColumnName（DisplayName）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.Description）.HasColumnName（Description）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.Sequence）.HasColumnName（Sequence）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.ExternalRef）.HasColumnName（ExternalRef）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.LoggedOn）.HasColumnName（LoggedOn）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.LoggedOff）.HasColumnName（LoggedOff）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.LastActivity）.HasColumnName（LastActivity）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.FailedLoginAttempts）.IsOptional（）。HasColumnName（FailedLoginAttempts）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.LockedOutUntil）.IsOptional（）。HasColumnName（LockedOutUntil）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.LockOutCycles）.IsOptional（）。HasColumnName（LockOutCycles）;
modelBuilder.Entity< ApplicationUser>（）。Property（au => au.Approved）.HasColumnName（Approved）;
modelBuilder.Entity< IdentityRole>（）。HasKey（ir => ir.Id）.ToTable（ApplicationRole，Users）;
modelBuilder.Entity< IdentityRole>（）。Property（ir => ir.Id）.HasColumnName（Id）;
modelBuilder.Entity< IdentityRole>（）。Property（ir => ir.Name）.HasColumnName（Name）;
modelBuilder.Entity< IdentityUserClaim>（）。HasKey（iuc => iuc.Id）.ToTable（UserClaim，Users）;
modelBuilder.Entity< IdentityUserClaim>（）。Property（iuc => iuc.Id）.HasColumnName（Id）;
modelBuilder.Entity< IdentityUserClaim>（）。Property（iuc => iuc.ClaimType）.HasColumnName（ClaimType）;
modelBuilder.Entity< IdentityUserClaim>（）。Property（iuc => iuc.ClaimValue）.HasColumnName（ClaimValue）;
modelBuilder.Entity< IdentityUserClaim>（）。Property（iuc => iuc.UserId）.HasColumnName（UserId）;
modelBuilder.Entity< IdentityUserLogin>（）。HasKey（iul => new {iul.UserId，iul.LoginProvider，iul.ProviderKey}）ToTable（UserLogin，Users）; //用于第三方OAuth提供者
modelBuilder.Entity< IdentityUserLogin>（）。Property（iul => iul.UserId）.HasColumnName（UserId）;
modelBuilder.Entity< IdentityUserLogin>（）。Property（iul => iul.LoginProvider）.HasColumnName（LoginProvider）;
modelBuilder.Entity< IdentityUserLogin>（）。Property（iul => iul.ProviderKey）.HasColumnName（ProviderKey）;
modelBuilder.Entity< IdentityUserRole>（）。HasKey（iur => new {iur.UserId，iur.RoleId}）ToTable（UserRole，Users）;
modelBuilder.Entity< IdentityUserRole>（）。Property（ur => ur.UserId）.HasColumnName（UserId）;
modelBuilder.Entity< IdentityUserRole>（）。Property（ur => ur.RoleId）.HasColumnName（RoleId）;
}
这个工作很好用于注册，确实登录了post注册，但任何尝试在此结果后登录 -
异常详细信息：System.Data.SqlClient.SqlException：
无效的列名称为IdentityUser_Id。
无效的列名称为IdentityUser_Id。
无效的列名称为Id。
无效的列名称为IdentityRole_Id。
无效的列名称为IdentityUser_Id。
源错误：
$ b第337行：{
行338：
AuthenticationManager.SignOut（DefaultAuthenticationTypes.ExternalCookie）;
第339行：
var identity = await UserManager.CreateIdentityAsync（user，DefaultAuthenticationTypes.ApplicationCookie）;
行340：
AuthenticationManager.SignIn（新的AuthenticationProperties {IsPersistent = isPersistent}，身份）;
行341：}
我相信这是一个问题ApplicationUser和IdentityUserRole实体之间的外键 - 这些实体存在于数据库中，但未在流畅的API映射中定义。 IdentityUser是一个复杂类型的事实似乎引导EF假设列被附加到IdentityUserRole表（命名为[User]。[UserRole]）的基础上生成的查询 -
exec sp_executesql N'SELECT
[Extent1]。[Id] AS [Id]，
[Extent1]。[UserId] AS [UserId ]，
[Extent1]。[RoleId] AS [RoleId]，
[Extent1]。[IdentityRole_Id] AS [IdentityRole_Id]，
[Extent1]。[IdentityUser_Id] AS [IdentityUser_Id]
FROM [Users]。[UserRole] AS [Extent1]
WHERE（[Extent1]。[IdentityUser_Id] IS NOT NULL）AND（[Extent1]。[IdentityUser_Id] =
@ EntityKeyValue1）' ，N'@ EntityKeyValue1 nvarchar（128）'，
@ EntityKeyValue1 = N'2e16b5b5-6604-4f2c-9fbb-7cc3f5c9d4f3'
go
如何在流动的API中配置来自ApplicationUser / IdentityUser和IdentityRole类的外键o指向[Users]。[User]表或从IdentityUserRole类返回给用户d角色实体或表？外键已经存在于SQL中。
解决方案
所以新的1.1-alpha1位将默认为接近于以下。这可能是你正在寻找的关于外键。注意：这有点不同于导航属性改变了一点，以便能够指定主键类型：
我们正在尝试解决一些EF迁移/可扩展性问题，所以事情希望将更容易与Identity 1.1-alpha1和即将到来的6.0.2 / 6.1 EF版本，但我不知道更新的EF包是否可用于myget。
var user = modelBuilder.Entity< TUser>（）
.ToTable（AspNetUsers）;
user.HasMany（u => u.Roles）.WithRequired（）。HasForeignKey（ur => ur.UserId）;
user.HasMany（u => u.Claims）.WithRequired（）。HasForeignKey（uc => uc.UserId）;
user.HasMany（u => u.Logins）.WithRequired（）。HasForeignKey（ul => ul.UserId）;
user.Property（u => u.UserName）.IsRequired（）;
modelBuilder.Entity< TUserRole>（）
.HasKey（r => new {r.UserId，r.RoleId}）
.ToTable（AspNetUserRoles）;
modelBuilder.Entity< TUserLogin>（）
.HasKey（l => new {l.UserId，l.LoginProvider，l.ProviderKey}）
.ToTable（ AspNetUserLogins）;
modelBuilder.Entity< TUserClaim>（）
.ToTable（AspNetUserClaims）;
var role = modelBuilder.Entity< TRole>（）
.ToTable（AspNetRoles）;
role.Property（r => r.Name）.IsRequired（）;
role.HasMany（r => r.Users）.WithRequired（）。HasForeignKey（ur => ur.RoleId）;
I am attempting to incorporate ASP.NET Identity into a new application that currently uses a SQL script to create the database schema. As we will need to create Foreign Key constraints from other tables to the user tables, it is highly desirable that the ASP.NET Identity tables are also created in the same scripts.
I have been able to extend the IdentityUser class in the ApplicationUser class created in IdentityModels.cs-
public class ApplicationUser : IdentityUser
{
public ApplicationUser()
{
Sequence = 0;
LastActivity = DateTime.Now;
}
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int NumericId
{
get;
set;
}
[MaxLength(50), Required]
public string DisplayName
{
get;
set;
}
[MaxLength(50), Required]
public string Description
{
get;
set;
}
[IntegerValidator(MinValue = 0), Required]
public int Sequence
{
get;
set;
}
[MaxLength(50)]
public string ExternalRef
{
get;
set;
}
public DateTime? LoggedOn
{
get;
set;
}
public DateTime? LoggedOff
{
get;
set;
}
public DateTime LastActivity
{
get;
set;
}
public int FailedLoginAttempts
{
get;
set;
}
public DateTime? LockedOutUntil
{
get;
set;
}
public int LockOutCycles
{
get;
set;
}
public bool Approved
{
get;
set;
}
}
I have created the tables using the script-
CREATE TABLE [Users].[User] (
[Id] [nvarchar](128) NOT NULL
,[NumericId] [int] IDENTITY(1,1) NOT NULL
,[UserName] [nvarchar](50) NULL
,[PasswordHash] [nvarchar](max) NULL
,[SecurityStamp] [nvarchar](max) NULL
,[DisplayName] [nvarchar](50) NULL
,[Description] [nvarchar](50) NOT NULL
,[EmailAddress] [nvarchar](254) NOT NULL
,[Confirmed] [bit] NOT NULL
,[Sequence] [int] NOT NULL
,[ExternalRef] [nvarchar](50) NOT NULL
,[LoggedOn] [datetime] NULL
,[LoggedOff] [datetime] NULL
,[LastActivity] [datetime] NULL
,[FailedLoginAttempts] [int] NOT NULL
,[LockedOutUntil] [datetime] NULL
,[LockOutCycles] int NOT NULL
,[Approved] [bit] NOT NULL
,[Discriminator] [nvarchar](128) NOT NULL
,CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
,CONSTRAINT [IX_User_NumericId] UNIQUE CLUSTERED ([NumericId] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
,CONSTRAINT [IX_User_Name] UNIQUE NONCLUSTERED ([UserName] 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
ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_Description] DEFAULT('')
FOR [Description]
GO
ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_Sequence] DEFAULT((0))
FOR [Sequence]
GO
ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_ExternalRef] DEFAULT('')
FOR [ExternalRef]
GO
ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_FailedLoginAttempts] DEFAULT((0))
FOR [FailedLoginAttempts]
GO
ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_LockOutCycles] DEFAULT((0))
FOR [LockOutCycles]
GO
CREATE NONCLUSTERED INDEX [IX_User_Sequence] ON [Users].[User] ([Sequence] ASC, [UserName] ASC)
WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO
CREATE TABLE [Users].[UserClaim](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
[UserId] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Users.UserClaims] 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] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Users].[UserClaim] WITH CHECK ADD CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id] FOREIGN KEY([UserId])
REFERENCES [Users].[User] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [Users].[UserClaim] CHECK CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id]
GO
CREATE TABLE [Users].[UserLogin](
[UserId] [nvarchar](128) NOT NULL,
[LoginProvider] [nvarchar](128) NOT NULL,
[ProviderKey] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Users.UserLogins] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[LoginProvider] ASC,
[ProviderKey] 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
ALTER TABLE [Users].[UserLogin] WITH CHECK ADD CONSTRAINT [FK_Users.UserLogins_Users.User_UserId] FOREIGN KEY([UserId])
REFERENCES [Users].[User] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [Users].[UserLogin] CHECK CONSTRAINT [FK_Users.UserLogins_Users.User_UserId]
CREATE TABLE [Users].[ApplicationRole](
[Id] [nvarchar](128) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Users.ApplicationRole] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [Users].[UserRole](
[UserId] [nvarchar](128) NOT NULL,
[RoleId] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Users.UserRole] 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) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Users].[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId] FOREIGN KEY([RoleId])
REFERENCES [Users].[ApplicationRole] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [Users].[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId]
GO
ALTER TABLE [Users].[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.User_UserId] FOREIGN KEY([UserId])
REFERENCES [Users].[User] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [Users].[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.User_UserId]
GO
CREATE TABLE [Users].[Department](
[Id] [int] IDENTITY(1, 1) NOT NULL
,[Name] [nvarchar](50) NOT NULL
,[Description] [nvarchar](50) NOT NULL
,[Sequence] [int] NOT NULL
CONSTRAINT [PK_Users.Department] 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
And have mapped the entities to the tables using an override on the OnModelCreating event-
public class ApplicationDbContext : IdentityDbContext<IdentityUser>
{
public ApplicationDbContext()
: base("DefaultConnection")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<IdentityUser>().ToTable("User", "Users");
modelBuilder.Entity<IdentityUser>().Property(iu => iu.Id).HasColumnName("Id");
modelBuilder.Entity<IdentityUser>().Property(iu => iu.UserName).HasColumnName("UserName");
modelBuilder.Entity<IdentityUser>().Property(iu => iu.Email).HasColumnName("EmailAddress").HasMaxLength(254).IsRequired();
modelBuilder.Entity<IdentityUser>().Property(iu => iu.PasswordHash).HasColumnName("PasswordHash");
modelBuilder.Entity<IdentityUser>().Property(iu => iu.SecurityStamp).HasColumnName("SecurityStamp");
modelBuilder.Entity<IdentityUser>().Property(iu => iu.IsConfirmed).HasColumnName("Confirmed");
modelBuilder.Entity<ApplicationUser>().HasKey(au => au.Id).ToTable("User", "Users"); //Specify our our own table names instead of the defaults
modelBuilder.Entity<ApplicationUser>().Property(au => au.Id).HasColumnName("Id");
modelBuilder.Entity<ApplicationUser>().Property(au => au.NumericId).HasColumnName("NumericId");
modelBuilder.Entity<ApplicationUser>().Property(au => au.UserName).HasMaxLength(50).HasColumnName("UserName");
modelBuilder.Entity<ApplicationUser>().Property(au => au.PasswordHash).HasColumnName("PasswordHash");
modelBuilder.Entity<ApplicationUser>().Property(au => au.SecurityStamp).HasColumnName("SecurityStamp");
modelBuilder.Entity<ApplicationUser>().Property(au => au.DisplayName).HasColumnName("DisplayName");
modelBuilder.Entity<ApplicationUser>().Property(au => au.Description).HasColumnName("Description");
modelBuilder.Entity<ApplicationUser>().Property(au => au.Sequence).HasColumnName("Sequence");
modelBuilder.Entity<ApplicationUser>().Property(au => au.ExternalRef).HasColumnName("ExternalRef");
modelBuilder.Entity<ApplicationUser>().Property(au => au.LoggedOn).HasColumnName("LoggedOn");
modelBuilder.Entity<ApplicationUser>().Property(au => au.LoggedOff).HasColumnName("LoggedOff");
modelBuilder.Entity<ApplicationUser>().Property(au => au.LastActivity).HasColumnName("LastActivity");
modelBuilder.Entity<ApplicationUser>().Property(au => au.FailedLoginAttempts).IsOptional().HasColumnName("FailedLoginAttempts");
modelBuilder.Entity<ApplicationUser>().Property(au => au.LockedOutUntil).IsOptional().HasColumnName("LockedOutUntil");
modelBuilder.Entity<ApplicationUser>().Property(au => au.LockOutCycles).IsOptional().HasColumnName("LockOutCycles");
modelBuilder.Entity<ApplicationUser>().Property(au => au.Approved).HasColumnName("Approved");
modelBuilder.Entity<IdentityRole>().HasKey(ir => ir.Id).ToTable("ApplicationRole", "Users");
modelBuilder.Entity<IdentityRole>().Property(ir => ir.Id).HasColumnName("Id");
modelBuilder.Entity<IdentityRole>().Property(ir => ir.Name).HasColumnName("Name");
modelBuilder.Entity<IdentityUserClaim>().HasKey(iuc => iuc.Id).ToTable("UserClaim", "Users");
modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.Id).HasColumnName("Id");
modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.ClaimType).HasColumnName("ClaimType");
modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.ClaimValue).HasColumnName("ClaimValue");
modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.UserId).HasColumnName("UserId");
modelBuilder.Entity<IdentityUserLogin>().HasKey(iul => new { iul.UserId, iul.LoginProvider, iul.ProviderKey }).ToTable("UserLogin", "Users"); //Used for third party OAuth providers
modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.UserId).HasColumnName("UserId");
modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.LoginProvider).HasColumnName("LoginProvider");
modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.ProviderKey).HasColumnName("ProviderKey");
modelBuilder.Entity<IdentityUserRole>().HasKey(iur => new { iur.UserId, iur.RoleId }).ToTable("UserRole", "Users");
modelBuilder.Entity<IdentityUserRole>().Property(ur => ur.UserId).HasColumnName("UserId");
modelBuilder.Entity<IdentityUserRole>().Property(ur => ur.RoleId).HasColumnName("RoleId");
}
This works well enough for registration, and indeed sign in post registration, but any attempt to sign in after this results in-
Exception Details: System.Data.SqlClient.SqlException:
Invalid column name 'IdentityUser_Id'.
Invalid column name 'IdentityUser_Id'.
Invalid column name 'Id'. Invalid column name 'IdentityRole_Id'.
Invalid column name 'IdentityUser_Id'.
Source Error:
Line 337: {
Line 338:
AuthenticationManager.SignOut(DefaultAuthenticationTypes.ExternalCookie);Line 339:
var identity = await UserManager.CreateIdentityAsync(user, DefaultAuthenticationTypes.ApplicationCookie);Line 340:
AuthenticationManager.SignIn(new AuthenticationProperties { IsPersistent = isPersistent }, identity);Line 341: }
I believe this is an issue with the Foreign Keys between the ApplicationUser and IdentityUserRole entities - these exist in the database but are not defined in the fluent API mappings. The fact that IdentityUser is a Complex Type seems to lead EF to assume that the columns are appended to the IdentityUserRole table (named "[User].[UserRole]") based on the generated query -
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId],
[Extent1].[IdentityRole_Id] AS [IdentityRole_Id],
[Extent1].[IdentityUser_Id] AS [IdentityUser_Id]
FROM [Users].[UserRole] AS [Extent1]
WHERE ([Extent1].[IdentityUser_Id] IS NOT NULL) AND ([Extent1].[IdentityUser_Id] =
@EntityKeyValue1)',N'@EntityKeyValue1 nvarchar(128)',
@EntityKeyValue1=N'2e16b5b5-6604-4f2c-9fbb-7cc3f5c9d4f3'
go
How can I configure the foreign keys in fluent API either from the ApplicationUser/IdentityUser and IdentityRole classes o point back to the "[Users].[User]" table, or from the IdentityUserRole class to point back to the user and role entities or tables? The foreign keys already exist in SQL.
解决方案
So the new 1.1-alpha1 bits will have something close to the following by default. This might be what you are looking for in regards to the Foreign Keys. Note: this is a bit different than 1.0 as the navigation properties changed a bit to enable the ability to specify the primary key type:
We are trying to address some of the EF migration/extensibility issues, so things hopefully will be easier with Identity 1.1-alpha1 and the upcoming 6.0.2/6.1 EF releases, but I'm not sure the updated EF packages are available on myget yet.
var user = modelBuilder.Entity<TUser>()
.ToTable("AspNetUsers");
user.HasMany(u => u.Roles).WithRequired().HasForeignKey(ur => ur.UserId);
user.HasMany(u => u.Claims).WithRequired().HasForeignKey(uc => uc.UserId);
user.HasMany(u => u.Logins).WithRequired().HasForeignKey(ul => ul.UserId);
user.Property(u => u.UserName).IsRequired();
modelBuilder.Entity<TUserRole>()
.HasKey(r => new { r.UserId, r.RoleId })
.ToTable("AspNetUserRoles");
modelBuilder.Entity<TUserLogin>()
.HasKey(l => new { l.UserId, l.LoginProvider, l.ProviderKey})
.ToTable("AspNetUserLogins");
modelBuilder.Entity<TUserClaim>()
.ToTable("AspNetUserClaims");
var role = modelBuilder.Entity<TRole>()
.ToTable("AspNetRoles");
role.Property(r => r.Name).IsRequired();
role.HasMany(r => r.Users).WithRequired().HasForeignKey(ur => ur.RoleId);
这篇关于使用SQL脚本创建ASP.NET身份表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
