sql-server – 使用SQL脚本创建ASP.NET身份表

前端之家收集整理的这篇文章主要介绍了sql-server – 使用SQL脚本创建ASP.NET身份表前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在尝试将ASP.NET Identity纳入到目前使用sql脚本创建数据库模式的新应用程序中.因为我们需要从其他表创建外键约束到用户表,所以非常希望ASP.NET身份表也是在相同的脚本中创建的.

我已经能够在IdentityMservices.cs创建的ApplicationUser类中扩展IdentityUser类,

  1. public class ApplicationUser : IdentityUser
  2. {
  3. public ApplicationUser()
  4. {
  5. Sequence = 0;
  6. LastActivity = DateTime.Now;
  7. }
  8.  
  9. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  10. public int NumericId
  11. {
  12. get;
  13. set;
  14. }
  15.  
  16. [MaxLength(50),required]
  17. public string DisplayName
  18. {
  19. get;
  20. set;
  21. }
  22.  
  23. [MaxLength(50),required]
  24. public string Description
  25. {
  26. get;
  27. set;
  28. }
  29.  
  30. [IntegerValidator(MinValue = 0),required]
  31. public int Sequence
  32. {
  33. get;
  34. set;
  35. }
  36.  
  37. [MaxLength(50)]
  38. public string ExternalRef
  39. {
  40. get;
  41. set;
  42. }
  43.  
  44. public DateTime? LoggedOn
  45. {
  46. get;
  47. set;
  48. }
  49.  
  50. public DateTime? LoggedOff
  51. {
  52. get;
  53. set;
  54. }
  55.  
  56. public DateTime LastActivity
  57. {
  58. get;
  59. set;
  60. }
  61.  
  62. public int FailedLoginAttempts
  63. {
  64. get;
  65. set;
  66. }
  67.  
  68. public DateTime? LockedOutUntil
  69. {
  70. get;
  71. set;
  72. }
  73.  
  74. public int LockOutCycles
  75. {
  76. get;
  77. set;
  78. }
  79.  
  80. public bool Approved
  81. {
  82. get;
  83. set;
  84. }
  85. }

我已经使用脚本创建了表,

  1. CREATE TABLE [Users].[User] (
  2. [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 (
  3. PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON
  4. ) ON [PRIMARY],CONSTRAINT [IX_User_NumericId] UNIQUE CLUSTERED ([NumericId] ASC) WITH (
  5. PAD_INDEX = OFF,CONSTRAINT [IX_User_Name] UNIQUE NONCLUSTERED ([UserName] ASC) WITH (
  6. PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON
  7. ) ON [PRIMARY]
  8. ) ON [PRIMARY]
  9. GO
  10.  
  11. ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_Description] DEFAULT('')
  12. FOR [Description]
  13. GO
  14.  
  15. ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_Sequence] DEFAULT((0))
  16. FOR [Sequence]
  17. GO
  18.  
  19. ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_ExternalRef] DEFAULT('')
  20. FOR [ExternalRef]
  21. GO
  22.  
  23. ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_FailedLoginAttempts] DEFAULT((0))
  24. FOR [FailedLoginAttempts]
  25. GO
  26.  
  27. ALTER TABLE [Users].[User] ADD CONSTRAINT [DF_User_LockOutCycles] DEFAULT((0))
  28. FOR [LockOutCycles]
  29. GO
  30.  
  31. CREATE NONCLUSTERED INDEX [IX_User_Sequence] ON [Users].[User] ([Sequence] ASC,[UserName] ASC)
  32. WITH (
  33. PAD_INDEX = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_PAGE_LOCKS = ON
  34. ) ON [PRIMARY]
  35. GO
  36.  
  37. CREATE TABLE [Users].[UserClaim](
  38. [Id] [int] IDENTITY(1,[ClaimType] [nvarchar](max) NULL,[ClaimValue] [nvarchar](max) NULL,[UserId] [nvarchar](128) NOT NULL,CONSTRAINT [PK_Users.UserClaims] PRIMARY KEY CLUSTERED
  39. (
  40. [Id] ASC
  41. )WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  42. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  43.  
  44. GO
  45.  
  46. ALTER TABLE [Users].[UserClaim] WITH CHECK ADD CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id] FOREIGN KEY([UserId])
  47. REFERENCES [Users].[User] ([Id])
  48. ON DELETE CASCADE
  49. GO
  50.  
  51. ALTER TABLE [Users].[UserClaim] CHECK CONSTRAINT [FK_Users.UserClaims_Users.User_User_Id]
  52. GO
  53.  
  54. CREATE TABLE [Users].[UserLogin](
  55. [UserId] [nvarchar](128) NOT NULL,[LoginProvider] [nvarchar](128) NOT NULL,[ProviderKey] [nvarchar](128) NOT NULL,CONSTRAINT [PK_Users.UserLogins] PRIMARY KEY CLUSTERED
  56. (
  57. [UserId] ASC,[LoginProvider] ASC,[ProviderKey] ASC
  58. )WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  59. ) ON [PRIMARY]
  60.  
  61. GO
  62.  
  63. ALTER TABLE [Users].[UserLogin] WITH CHECK ADD CONSTRAINT [FK_Users.UserLogins_Users.User_UserId] FOREIGN KEY([UserId])
  64. REFERENCES [Users].[User] ([Id])
  65. ON DELETE CASCADE
  66. GO
  67.  
  68. ALTER TABLE [Users].[UserLogin] CHECK CONSTRAINT [FK_Users.UserLogins_Users.User_UserId]
  69.  
  70. CREATE TABLE [Users].[ApplicationRole](
  71. [Id] [nvarchar](128) NOT NULL,[Name] [nvarchar](max) NOT NULL,CONSTRAINT [PK_Users.ApplicationRole] PRIMARY KEY CLUSTERED
  72. (
  73. [Id] ASC
  74. )WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  75. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  76.  
  77.  
  78. CREATE TABLE [Users].[UserRole](
  79. [UserId] [nvarchar](128) NOT NULL,[RoleId] [nvarchar](128) NOT NULL,CONSTRAINT [PK_Users.UserRole] PRIMARY KEY CLUSTERED
  80. (
  81. [UserId] ASC,[RoleId] ASC
  82. )WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  83. ) ON [PRIMARY]
  84.  
  85. GO
  86.  
  87. ALTER TABLE [Users].[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId] FOREIGN KEY([RoleId])
  88. REFERENCES [Users].[ApplicationRole] ([Id])
  89. ON DELETE CASCADE
  90. GO
  91.  
  92. ALTER TABLE [Users].[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.ApplicationRole_RoleId]
  93. GO
  94.  
  95. ALTER TABLE [Users].[UserRole] WITH CHECK ADD CONSTRAINT [FK_Users.UserRole_Users.User_UserId] FOREIGN KEY([UserId])
  96. REFERENCES [Users].[User] ([Id])
  97. ON DELETE CASCADE
  98. GO
  99.  
  100. ALTER TABLE [Users].[UserRole] CHECK CONSTRAINT [FK_Users.UserRole_Users.User_UserId]
  101. GO
  102.  
  103. CREATE TABLE [Users].[Department](
  104. [Id] [int] IDENTITY(1,[Name] [nvarchar](50) NOT NULL,[Sequence] [int] NOT NULL
  105. CONSTRAINT [PK_Users.Department] PRIMARY KEY CLUSTERED
  106. (
  107. [Id] ASC
  108. )WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  109. ) ON [PRIMARY]
  110.  
  111. GO

并且使用OnModelCreating事件的覆盖将实体映射到表,

  1. public class ApplicationDbContext : IdentityDbContext<IdentityUser>
  2. {
  3. public ApplicationDbContext()
  4. : base("DefaultConnection")
  5. {
  6. }
  7.  
  8. protected override void OnModelCreating(DbModelBuilder modelBuilder)
  9. {
  10. modelBuilder.Entity<IdentityUser>().ToTable("User","Users");
  11.  
  12. modelBuilder.Entity<IdentityUser>().Property(iu => iu.Id).HasColumnName("Id");
  13. modelBuilder.Entity<IdentityUser>().Property(iu => iu.UserName).HasColumnName("UserName");
  14. modelBuilder.Entity<IdentityUser>().Property(iu => iu.Email).HasColumnName("EmailAddress").HasMaxLength(254).Isrequired();
  15. modelBuilder.Entity<IdentityUser>().Property(iu => iu.PasswordHash).HasColumnName("PasswordHash");
  16. modelBuilder.Entity<IdentityUser>().Property(iu => iu.SecurityStamp).HasColumnName("SecurityStamp");
  17. modelBuilder.Entity<IdentityUser>().Property(iu => iu.IsConfirmed).HasColumnName("Confirmed");
  18.  
  19. modelBuilder.Entity<ApplicationUser>().HasKey(au => au.Id).ToTable("User","Users"); //Specify our our own table names instead of the defaults
  20.  
  21. modelBuilder.Entity<ApplicationUser>().Property(au => au.Id).HasColumnName("Id");
  22. modelBuilder.Entity<ApplicationUser>().Property(au => au.NumericId).HasColumnName("NumericId");
  23. modelBuilder.Entity<ApplicationUser>().Property(au => au.UserName).HasMaxLength(50).HasColumnName("UserName");
  24. modelBuilder.Entity<ApplicationUser>().Property(au => au.PasswordHash).HasColumnName("PasswordHash");
  25. modelBuilder.Entity<ApplicationUser>().Property(au => au.SecurityStamp).HasColumnName("SecurityStamp");
  26. modelBuilder.Entity<ApplicationUser>().Property(au => au.DisplayName).HasColumnName("DisplayName");
  27. modelBuilder.Entity<ApplicationUser>().Property(au => au.Description).HasColumnName("Description");
  28. modelBuilder.Entity<ApplicationUser>().Property(au => au.Sequence).HasColumnName("Sequence");
  29. modelBuilder.Entity<ApplicationUser>().Property(au => au.ExternalRef).HasColumnName("ExternalRef");
  30. modelBuilder.Entity<ApplicationUser>().Property(au => au.LoggedOn).HasColumnName("LoggedOn");
  31. modelBuilder.Entity<ApplicationUser>().Property(au => au.LoggedOff).HasColumnName("LoggedOff");
  32. modelBuilder.Entity<ApplicationUser>().Property(au => au.LastActivity).HasColumnName("LastActivity");
  33. modelBuilder.Entity<ApplicationUser>().Property(au => au.FailedLoginAttempts).IsOptional().HasColumnName("FailedLoginAttempts");
  34. modelBuilder.Entity<ApplicationUser>().Property(au => au.LockedOutUntil).IsOptional().HasColumnName("LockedOutUntil");
  35. modelBuilder.Entity<ApplicationUser>().Property(au => au.LockOutCycles).IsOptional().HasColumnName("LockOutCycles");
  36. modelBuilder.Entity<ApplicationUser>().Property(au => au.Approved).HasColumnName("Approved");
  37.  
  38. modelBuilder.Entity<IdentityRole>().HasKey(ir => ir.Id).ToTable("ApplicationRole","Users");
  39.  
  40. modelBuilder.Entity<IdentityRole>().Property(ir => ir.Id).HasColumnName("Id");
  41. modelBuilder.Entity<IdentityRole>().Property(ir => ir.Name).HasColumnName("Name");
  42.  
  43. modelBuilder.Entity<IdentityUserClaim>().HasKey(iuc => iuc.Id).ToTable("UserClaim","Users");
  44.  
  45. modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.Id).HasColumnName("Id");
  46. modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.ClaimType).HasColumnName("ClaimType");
  47. modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.ClaimValue).HasColumnName("ClaimValue");
  48. modelBuilder.Entity<IdentityUserClaim>().Property(iuc => iuc.UserId).HasColumnName("UserId");
  49.  
  50. modelBuilder.Entity<IdentityUserLogin>().HasKey(iul => new { iul.UserId,iul.LoginProvider,iul.ProviderKey }).ToTable("UserLogin","Users"); //Used for third party OAuth providers
  51.  
  52. modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.UserId).HasColumnName("UserId");
  53. modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.LoginProvider).HasColumnName("LoginProvider");
  54. modelBuilder.Entity<IdentityUserLogin>().Property(iul => iul.ProviderKey).HasColumnName("ProviderKey");
  55.  
  56. modelBuilder.Entity<IdentityUserRole>().HasKey(iur => new { iur.UserId,iur.RoleId }).ToTable("UserRole","Users");
  57.  
  58. modelBuilder.Entity<IdentityUserRole>().Property(ur => ur.UserId).HasColumnName("UserId");
  59. modelBuilder.Entity<IdentityUserRole>().Property(ur => ur.RoleId).HasColumnName("RoleId");
  60. }

这对于注册工作很好,确实登录注册后,但是任何企图登录后,

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]”)的基础上生成查询

  1. exec sp_executesql N'SELECT
  2. [Extent1].[Id] AS [Id],[Extent1].[UserId] AS [UserId],[Extent1].[RoleId] AS [RoleId],[Extent1].[IdentityRole_Id] AS [IdentityRole_Id],[Extent1].[IdentityUser_Id] AS [IdentityUser_Id]
  3. FROM [Users].[UserRole] AS [Extent1]
  4. WHERE ([Extent1].[IdentityUser_Id] IS NOT NULL) AND ([Extent1].[IdentityUser_Id] =
  5. @EntityKeyValue1)',N'@EntityKeyValue1 nvarchar(128)',@EntityKeyValue1=N'2e16b5b5-6604-4f2c-9fbb-7cc3f5c9d4f3'
  6. go

如何从流行的API中配置外键,或者从ApplicationUser / IdentityUser和IdentityRole类中指定“[Users].[User]”表,或者从IdentityUserRole类返回到用户和角色实体或表?外键已经存在于sql中.

解决方法

所以新的1.1-alpha1位将默认接近以下.这可能是你正在寻找的关于外键.注意:与导航属性更改位有点不同,以便能够指定主键类型:

我们正在努力解决一些EF迁移/可扩展性问题,所以希望通过Identity 1.1-alpha1和即将推出的6.0.2 / 6.1 EF版本更容易,但我不知道更新的EF包是否可用于myget然而.

  1. var user = modelBuilder.Entity<TUser>()
  2. .ToTable("AspNetUsers");
  3. user.HasMany(u => u.Roles).Withrequired().HasForeignKey(ur => ur.UserId);
  4. user.HasMany(u => u.Claims).Withrequired().HasForeignKey(uc => uc.UserId);
  5. user.HasMany(u => u.Logins).Withrequired().HasForeignKey(ul => ul.UserId);
  6. user.Property(u => u.UserName).Isrequired();
  7.  
  8. modelBuilder.Entity<TUserRole>()
  9. .HasKey(r => new { r.UserId,r.RoleId })
  10. .ToTable("AspNetUserRoles");
  11.  
  12. modelBuilder.Entity<TUserLogin>()
  13. .HasKey(l => new { l.UserId,l.LoginProvider,l.ProviderKey})
  14. .ToTable("AspNetUserLogins");
  15.  
  16. modelBuilder.Entity<TUserClaim>()
  17. .ToTable("AspNetUserClaims");
  18.  
  19. var role = modelBuilder.Entity<TRole>()
  20. .ToTable("AspNetRoles");
  21. role.Property(r => r.Name).Isrequired();
  22. role.HasMany(r => r.Users).Withrequired().HasForeignKey(ur => ur.RoleId);

猜你在找的MsSQL相关文章