我已经能够在IdentityMservices.cs创建的ApplicationUser类中扩展IdentityUser类,
- 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;
- }
- }
我已经使用脚本创建了表,
- 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,CONSTRAINT [IX_User_Name] UNIQUE NONCLUSTERED ([UserName] ASC) WITH (
- PAD_INDEX = OFF,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,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_PAGE_LOCKS = ON
- ) ON [PRIMARY]
- GO
- CREATE TABLE [Users].[UserClaim](
- [Id] [int] IDENTITY(1,[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,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,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,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,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,[Name] [nvarchar](50) NOT NULL,[Sequence] [int] NOT NULL
- CONSTRAINT [PK_Users.Department] PRIMARY KEY CLUSTERED
- (
- [Id] ASC
- )WITH (PAD_INDEX = OFF,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("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");
- }
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: }
我相信这是ApplicationUser和IdentityUserRole实体之间的Foreign Keys的问题 – 这些实体存在于数据库中,但在流畅的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类中指定“[Users].[User]”表,或者从IdentityUserRole类返回到用户和角色实体或表?外键已经存在于sql中.
解决方法
我们正在努力解决一些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);