sql server - 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");
    

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