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.