Create ASP.NET Identity tables using SQL script

Question

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.

Answer 1

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);

Answer 2

The final (working) OnModelCreating method builds on the answer provided by Hao Kung (whose answer I have accepted and to whom I have awarded the bounty) to create the Foreign Key references from the IdentityUser/ApplicationUser side rather than attempting to do the same from the other direction.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    var user = modelBuilder.Entity<IdentityUser>().HasKey(u => u.Id).ToTable("User", "Users"); //Specify our our own table names instead of the defaults

    user.Property(iu => iu.Id).HasColumnName("Id");
    user.Property(iu => iu.UserName).HasColumnName("UserName");
    user.Property(iu => iu.Email).HasColumnName("EmailAddress").HasMaxLength(254).IsRequired();
    user.Property(iu => iu.IsConfirmed).HasColumnName("EmailConfirmed");
    user.Property(iu => iu.PasswordHash).HasColumnName("PasswordHash");
    user.Property(iu => iu.SecurityStamp).HasColumnName("SecurityStamp");

    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();

    var applicationUser = modelBuilder.Entity<ApplicationUser>().HasKey(au => au.Id).ToTable("User", "Users"); //Specify our our own table names instead of the defaults

    applicationUser.Property(au => au.Id).HasColumnName("Id");
    applicationUser.Property(au => au.NumericId).HasColumnName("NumericId");
    applicationUser.Property(au => au.UserName).HasMaxLength(50).HasColumnName("UserName");
    applicationUser.Property(au => au.PasswordHash).HasColumnName("PasswordHash");
    applicationUser.Property(au => au.SecurityStamp).HasColumnName("SecurityStamp");
    applicationUser.Property(au => au.DisplayName).HasColumnName("DisplayName");
    applicationUser.Property(au => au.Description).HasColumnName("Description");
    applicationUser.Property(au => au.Email).HasColumnName("EmailAddress").HasMaxLength(254).IsRequired();
    applicationUser.Property(au => au.IsConfirmed).HasColumnName("EmailConfirmed");
    applicationUser.Property(au => au.Sequence).HasColumnName("Sequence");
    applicationUser.Property(au => au.ExternalRef).HasColumnName("ExternalRef");
    applicationUser.Property(au => au.LoggedOn).HasColumnName("LoggedOn");
    applicationUser.Property(au => au.LoggedOff).HasColumnName("LoggedOff");
    applicationUser.Property(au => au.LastActivity).HasColumnName("LastActivity");
    applicationUser.Property(au => au.FailedLoginAttempts).IsOptional().HasColumnName("FailedLoginAttempts");
    applicationUser.Property(au => au.LockedOutUntil).IsOptional().HasColumnName("LockedOutUntil");
    applicationUser.Property(au => au.LockOutCycles).IsOptional().HasColumnName("LockOutCycles");
    applicationUser.Property(au => au.Approved).HasColumnName("Approved");

    var role = modelBuilder.Entity<IdentityRole>().HasKey(ir => ir.Id).ToTable("ApplicationRole", "Users");

    role.Property(ir => ir.Id).HasColumnName("Id");
    role.Property(ir => ir.Name).HasColumnName("Name");

    var claim = modelBuilder.Entity<IdentityUserClaim>().HasKey(iuc => iuc.Id).ToTable("UserClaim", "Users");

    claim.Property(iuc => iuc.Id).HasColumnName("Id");
    claim.Property(iuc => iuc.ClaimType).HasColumnName("ClaimType");
    claim.Property(iuc => iuc.ClaimValue).HasColumnName("ClaimValue");
    claim.Property(iuc => iuc.UserId).HasColumnName("UserId");

    var login = modelBuilder.Entity<IdentityUserLogin>().HasKey(iul => new { iul.UserId, iul.LoginProvider, iul.ProviderKey }).ToTable("UserLogin", "Users"); //Used for third party OAuth providers

    login.Property(iul => iul.UserId).HasColumnName("UserId");
    login.Property(iul => iul.LoginProvider).HasColumnName("LoginProvider");
    login.Property(iul => iul.ProviderKey).HasColumnName("ProviderKey");

    var userRole = modelBuilder.Entity<IdentityUserRole>().HasKey(iur => new { iur.UserId, iur.RoleId }).ToTable("UserRole", "Users");

    userRole.Property(ur => ur.UserId).HasColumnName("UserId");
    userRole.Property(ur => ur.RoleId).HasColumnName("RoleId");
}

I also liked the (brilliant yet so simple I can't think why I didn't do it before) idea of referencing the Model Builder Entity as a variable to use on each following fluent-api statement.

I referenced properties and foreign keys on both entities, although (in practice) it does not appear that this is required. On ASP.NET Identity 1.0 there was no problem omitting the property mappings, but since I have changed the names of Email and IsConfirmed database columns at least those needed to be mapped against the IdentityUser entity in ASP.NET Identity 1.1

Likewise the ApplicationUser class probably does not need to foreign key defined (as relationships are defined between the base class and the other entities) but I saw no harm in ensuring there was no assumption of additional foreign key columns on the other entity tables by adding a explicit definition - and it doesn't seem to have done any harm.