Asp.Net Identity Database First Approach

Development | Luka Erkapic

Asp.Net Identity Database First Approach

Friday, Nov 17, 2017 • 3 min read

How to create ASP.NET Identity tables using Entity Framework Database First Approach.

In one of our projects, we are using ASP.NET Identity to handle user authentication - which is still a preferred way to do security when using .NET framework. In this post, we will use Entity Framework Database First Approach, along with EntityFramework Reverse POCO Generator to generate context and POCO classes.

Creating database and tables

The first step is to open MS SQL Server Manager and create an example database with ASP.NET Identity tables. Let’s name the database TestDatabase for the purpose of the article.

Now we can create table scripts, which should be run in the following order:

ApplicationUsers
ApplicationRoles
ApplicationUserRoles
ApplicationUserLogins
ApplicationUserClaims

Also, take note that the primary key for all tables will be of int data type. The default type for ASP.NET Identity is string, but can easily be changed to any other type (which we will show later).

For now, run the following queries in SQL Manager:

CREATE TABLE [dbo].[ApplicationUsers] (
    [Id]                   INT            IDENTITY (1, 1) NOT NULL,
    [Email]                NVARCHAR (256) NULL,
    [EmailConfirmed]       BIT            NOT NULL,
    [PasswordHash]         NVARCHAR (512) NULL,
    [SecurityStamp]        NVARCHAR (512) NULL,
    [PhoneNumber]          NVARCHAR (128) NULL,
    [PhoneNumberConfirmed] BIT            NOT NULL,
    [TwoFactorEnabled]     BIT            NOT NULL,
    [LockoutEndDateUtc]    DATETIME       NULL,
    [LockoutEnabled]       BIT            NOT NULL,
    [AccessFailedCount]    INT            NOT NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    [FirstName]            NVARCHAR (256) NULL,
    [LastName]             NVARCHAR (256) NULL,
    [DateCreated]          DATETIME       NOT NULL,
    [DateUpdated]          DATETIME       NOT NULL,
    [LastLoginDate]        DATETIME       NULL,
    [PasswordChangeDate]   DATETIME       NULL,
    CONSTRAINT [PK_ApplicationUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UQ_ApplicationUsers_Email] UNIQUE NONCLUSTERED ([Email] ASC),
    CONSTRAINT [UQ_ApplicationUsers_UserName] UNIQUE NONCLUSTERED ([UserName] ASC)
);

CREATE TABLE [dbo].[ApplicationRoles] (
    [Id]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_ApplicationRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
    ON [dbo].[ApplicationRoles]([Name] ASC);

CREATE TABLE [dbo].[ApplicationUserRoles] (
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    CONSTRAINT [PK_dbo.ApplicationUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [FK_dbo.ApplicationUserRoles_dbo.ApplicationRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[ApplicationRoles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.ApplicationUserRoles_dbo.ApplicationUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[ApplicationUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[ApplicationUserRoles]([UserId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[ApplicationUserRoles]([RoleId] ASC);

CREATE TABLE [dbo].[ApplicationUserLogins] (
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL,
    [UserId]        INT            NOT NULL,
    CONSTRAINT [PK_dbo.ApplicationUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC),
    CONSTRAINT [FK_dbo.ApplicationUserLogins_dbo.ApplicationUsers_Id] FOREIGN KEY ([UserId]) REFERENCES [dbo].[ApplicationUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[ApplicationUserLogins]([UserId] ASC);

CREATE TABLE [dbo].[ApplicationUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [UserId]     INT            NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_ApplicationUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ApplicationUserClaims_ApplicationUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[ApplicationUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[ApplicationUserClaims]([UserId] ASC);

With that done, the database should now contain five tables associated with Identity Manager.

Reverse Engineer Database

Creating mappings to the database is rather easy with EntityFramework Reverse Poco Generator, and requires a couple of steps:

Install EntityFramework Reverse POCO Generator template,
Create a project in Visual Studio,
Add Microsoft ASP.NET Identity EntityFramework using Install-Package Microsoft.AspNet.Identity.EntityFramework command in the Package Manager Console,

Add connection string to app.config or web.config:

<connectionStrings>
    <add name="DatabaseContext" connectionString="Server=.;Database=TestDatabase;User ID=your_username;Password=your_password;" providerName="System.Data.SqlClient" />
</connectionStrings>

Add EntityFramework Reverse POCO Code First Generator file from the New Item menu:

EntityFramework Reverse POCO Code First Generator

When an item is added, the database.tt file should be created. A couple of variables should be changed in the database.tt file:

Set ConnectionStringName to name of the connection string, DatabaseContext in this case,
Set UseMappingTables to true, and
Set DbContextBaseClass to Microsoft.AspNet.Identity.EntityFramework.IdentityDbContext<ApplicationUser, ApplicationRole, int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>.

Note that third generic argument, when inheriting IdentityDbContext, is of type int, which is a way of telling Identity Manager which data type is used for the primary key.

If everything went ok, database.cs file with context, mapping, and POCO classes should be generated and ready for use with Identity Manager. Creating Identity tables with Code First Approach is great, but most projects already have an existing database, and using Database First Approach for such projects seems like a better option. I hope that you found this post helpful. Also, I would like to hear about your experiences with Identity and Database First Approach in the comments section below.