Setting Up ASP.NET Identity Framework 2.0 with Database First (VS2013 Update 2 SPA Template)

Table of Contents

Update (2/12/15)

Important: This article assumes you intend to use the project template’s default Identity Framework 2.0 implementation, which, as an example, uses a string as the UserId’s primary key. If an integer is desired instead, then follow this Microsoft article before proceeding to Step 2. Furthermore, if you intend to copy the provided database table scripts from this article, keep in mind they are for the default string as a primary key implementation. It is recommended to create the scripts yourself to get the correct integer primary key code. Finally, keep in mind the instructions from the Microsoft article may vary depending on what update you have installed for Visual Studio 2013 (Update 2 and Update 3 have variations in implementation and instructions).

Introduction

With the recent release of ASP.NET Identity 2.0 some great new features have come out of the wood work such as Two-factor Authentication, Account Lockout, Account Confirmation, and Password Reset among others. A nice blog post from the .NET Web Development and Tools Group provides more details. However, as nice as these new features are, integrating Identity Framework 2.0 with an existing database isn’t easy. I spent several hours working on this and have finally found a working solution which I’m going to share.

I ended up using the ASP.NET MVC 5 SPA Template found in Visual Studio 2013 Update 2 which was just released (at the time of this writing). If you are interested in getting into the responsive single page application world, which is much the hype these days, it is a fantastic starting point. If this isn’t your intention, most of the points found throughout this post should apply to many projects.

Step 1 — Create the ASP.NET MVC 5 Application Using the SPA Template

To begin, assuming you have already installed Update 2 for Visual Studio 2013, create a new ASP.NET MVC Web Application.

Figure 1.0 - Create Solution/Project — Figure 1.0 — Create Solution/Project

[spacer height=”20px”]Next, select Single Page Application from the available templates and leave everything set to default.

Figure 1.1 - Select SPA Template — Figure 1.1 — Select SPA Template

[spacer height=”20px”]When the project creation has finished, you will end up with what you see in the screen capture below.

Figure 1.3 - Created Project — Figure 1.2 — Created Project

Step 2 — Build and Run the Application

At this point, you will need to build the application (CTRL + F5) and it should come up in your browser of choice. In my case, Chrome as shown below.

Figure 1.4 - Application Shown in Chrome — Figure 1.3 — Application Shown in Chrome

[spacer height=”20px”]Click on the link that says Register as a new user and then fill out the information to create an account.

Figure 1.5 - Register a New User — Figure 1.4 — Register a New User

Step 3 — Inspect the Web.config File Connection String and Connect to Local Database

Automatically, the application will create a database (using Local DB: (LocalDb)\v11.0) with all the necessary Identity Framework 2.0 tables upon registering a new user. Open the Web.Config file in the base of the project and make note of the connection string. In my case, the below connection string was created.

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-SampleApp.Web-20140515030919.mdf;Initial Catalog=aspnet-SampleApp.Web-20140515030919;Integrated Security=True" providerName="System.Data.SqlClient" />

1	<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=\|DataDirectory\|\aspnet-SampleApp.Web-20140515030919.mdf;Initial Catalog=aspnet-SampleApp.Web-20140515030919;Integrated Security=True" providerName="System.Data.SqlClient" />

We are interested in the name of the database, which turned out to be aspnet-SampleApp.Web-20140515030919 which is also the name of the MDF file which stores the database. This file is found in the App_Data directory of the project.

Now it’s time to fire up SQL Server Management Studio and connect to the local database. Ensure your connection looks like the one below.

Note: As an alternative, you can use the SQL tools (SSDT) which are included with Visual Studio 2013 to create the scripts for the tables.

Figure 1.6 - SQL Connection Settings — Figure 1.5 — SQL Connection Settings

[spacer height=”20px”]Once authenticated, look for the database which matched the name of the connection string.

Figure 1.7 - LocalDb Database — Figure 1.6 — LocalDb Database

[spacer height=”20px”]Upon expanding the tables folder of the database, you will find the following auto-generated tables under the dbo schema.

dbo._MigrationHistory
dbo.AspNetRoles
dbo.AspNetUserClaims
dbo.AspNetUserLogins
dbo.AspNetUserRoles
dbo.AspNetUsers

[spacer height=”20px”]

Figure 1.8 - Identity 2.0 Tables — Figure 1.7 — Identity 2.0 Tables

Step 4 — Generate SQL Scripts For Each Database Table

We need to get some information on the Identity Framework 2.0 tables and their structure to recreate these tables in our database. This is done by creating SQL scripts for each table, which can later be run in a query on our database.

Right click on each table and select the following: Script Table as → CREATE To → File. Give the script a name (I chose the name of the table) and save it. Each table’s script has been included here for your convenience.

dbo._MigrationHistory:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[__MigrationHistory]    Script Date: 5/15/2014 3:57:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[__MigrationHistory](
    [MigrationId] [nvarchar](150) NOT NULL,
    [ContextKey] [nvarchar](300) NOT NULL,
    [Model] [varbinary](max) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED 
(
    [MigrationId] ASC,
    [ContextKey] 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

SET ANSI_PADDING OFF
GO

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[__MigrationHistory] Script Date: 5/15/2014 3:57:55 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[__MigrationHistory](

[MigrationId] [nvarchar](150) NOT NULL,

[ContextKey] [nvarchar](300) NOT NULL,

[Model] [varbinary](max) NOT NULL,

[ProductVersion] [nvarchar](32) NOT NULL,

CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED

(

[MigrationId] ASC,

[ContextKey] 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]

SET ANSI_PADDING OFF

dbo.AspNetRoles:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[AspNetRoles]    Script Date: 5/15/2014 4:04:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetRoles](
    [Id] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetRoles] 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

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[AspNetRoles] Script Date: 5/15/2014 4:04:57 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[AspNetRoles](

[Id] [nvarchar](128) NOT NULL,

[Name] [nvarchar](256) NOT NULL,

CONSTRAINT [PK_dbo.AspNetRoles] 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]

dbo.AspNetUserClaims:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[AspNetUserClaims]    Script Date: 5/15/2014 4:05:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserClaims](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [nvarchar](128) NOT NULL,
    [ClaimType] [nvarchar](max) NULL,
    [ClaimValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.AspNetUserClaims] 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 [dbo].[AspNetUserClaims]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
GO

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[AspNetUserClaims] Script Date: 5/15/2014 4:05:11 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[AspNetUserClaims](

[Id] [int] IDENTITY(1,1) NOT NULL,

[UserId] [nvarchar](128) NOT NULL,

[ClaimType] [nvarchar](max) NULL,

[ClaimValue] [nvarchar](max) NULL,

CONSTRAINT [PK_dbo.AspNetUserClaims] 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]

ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])

REFERENCES [dbo].[AspNetUsers] ([Id])

ON DELETE CASCADE

ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]

dbo.AspNetUserLogins:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[AspNetUserLogins]    Script Date: 5/15/2014 4:05:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserLogins](
    [LoginProvider] [nvarchar](128) NOT NULL,
    [ProviderKey] [nvarchar](128) NOT NULL,
    [UserId] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED 
(
    [LoginProvider] ASC,
    [ProviderKey] ASC,
    [UserId] 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 [dbo].[AspNetUserLogins]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
GO

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[AspNetUserLogins] Script Date: 5/15/2014 4:05:37 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[AspNetUserLogins](

[LoginProvider] [nvarchar](128) NOT NULL,

[ProviderKey] [nvarchar](128) NOT NULL,

[UserId] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED

(

[LoginProvider] ASC,

[ProviderKey] ASC,

[UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])

REFERENCES [dbo].[AspNetUsers] ([Id])

ON DELETE CASCADE

ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]

dbo.AspNetUserRoles:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[AspNetUserRoles]    Script Date: 5/15/2014 4:05:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserRoles](
    [UserId] [nvarchar](128) NOT NULL,
    [RoleId] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserRoles] 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 [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
GO

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
GO

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[AspNetUserRoles] Script Date: 5/15/2014 4:05:50 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[AspNetUserRoles](

[UserId] [nvarchar](128) NOT NULL,

[RoleId] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_dbo.AspNetUserRoles] 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]

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])

REFERENCES [dbo].[AspNetRoles] ([Id])

ON DELETE CASCADE

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])

REFERENCES [dbo].[AspNetUsers] ([Id])

ON DELETE CASCADE

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]

dbo.AspNetUsers:

USE [aspnet-SampleApp.Web-20140515030919]
GO

/****** Object:  Table [dbo].[AspNetUsers]    Script Date: 5/15/2014 4:06:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsers](
    [Id] [nvarchar](128) NOT NULL,
    [Hometown] [nvarchar](max) NULL,
    [Email] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) 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,
 CONSTRAINT [PK_dbo.AspNetUsers] 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

USE [aspnet-SampleApp.Web-20140515030919]

/****** Object: Table [dbo].[AspNetUsers] Script Date: 5/15/2014 4:06:02 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[AspNetUsers](

[Id] [nvarchar](128) NOT NULL,

[Hometown] [nvarchar](max) NULL,

[Email] [nvarchar](256) NULL,

[EmailConfirmed] [bit] NOT NULL,

[PasswordHash] [nvarchar](max) NULL,

[SecurityStamp] [nvarchar](max) NULL,

[PhoneNumber] [nvarchar](max) 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,

CONSTRAINT [PK_dbo.AspNetUsers] 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]

Step 5 – Connect to Desired Database and Run SQL Scripts

Since this post is about Database First, connect to the database you wish to use. You will need to run the SQL scripts to create the Identity Framework 2.0 tables. The recommended order to run these scripts is shown below. If you don’t follow this order you may run into errors.

dbo._MigrationHistory
dbo.AspNetUsers
dbo.AspNetRoles
dbo.AspNetUserClaims
dbo.AspNetUserLogins
dbo.AspNetUserRoles

[spacer height=”20px”]

At this point, if you need to add any relationships (e.g., foreign keys) from your tables to these tables you are welcome to do so but do not modify any of the Entity Framework 2.0 tables directly or later on any of their POCO classes. Doing so will result in errors based upon feedback I’ve received.

Step 6 — Create Second Project For EDMX/Data Model

It is recommended and often common practice to separate the different portions of an application into different projects. In this case, we already created one named SampleApp.Web which is the main MVC project. The next project we will create will be SampleApp.Data which we will use to house the EDMX file to generate our POCO classes and database context logic.

Note: In a production environment, it would be a good idea to create a third project named SampleApp.Domain to house only the POCO classes. This can be a bit tricky with the EDMX since POCO classes are auto-generated into that same project. Julie Lerman has a great Pluralsight course called Getting Started With Entity Framework 5 which discusses two methods for doing this, one which is rather messy and then the preferred, more elegant method. You really don’t want any references to Entity Framework in your SampleApp.Domain project and only the POCO classes themselves, which other projects refer to when they need them. This is best practice.

Right-click on the solution and select Add → New Project and select Class Library. As mentioned above, I have given the project the name SampleApp.Data.

Figure 1.9 - Create Class Library Project — Figure 1.8 — Create Class Library Project

[spacer height=”20px”]Before doing anything else, right-click on the web project (in my case SampleApp.Web) and select Set as StartUp Project. This will make sure when the project is built, it actually runs. Moreover, add a reference to the new class library project (SampleApp.Data) to the web project.

In addition, delete the Class1.cs file that was automatically created in the root of the project, as it’s not needed.

Step 7 — Create ADO.NET Entity Data Model

Add a new folder to the data project named DAL, short for data access layer. Keep in mind, you don’t have to follow my convention or even put it into a separate folder. Right-click on that folder and select Add → New Item. Select ADO.NET Entity Data Model and give it a name. In this case, I have given it the name SampleAppModel.

Figure 2.0 - Create ADO.NET Entity Data Model — Figure 1.9 — Create ADO.NET Entity Data Model

[spacer height=”20px”]The Entity Data Model Wizard will pop up asking what the model should contain. Select EF Designer from the database.

Figure 2.1 - Entity Data Model Wizard — Figure 2.0 — Entity Data Model Wizard

[spacer height=”20px”]Upon clicking next, you will have to specify the connection information to your desired existing database. Do so and then provide a name for the connection as shown below. I chose the name SampleAppDbContext.

Figure 2.1 — Connection Name to Store in App.Config

[spacer height=”20px”]Note: You may be asked what version of Entity Framework you wish to use. I highly suggest using Entity Framework 6.0.

Next, the Entity Data Model Wizard will ask you which database objects you want to include in your model. Select all the tables in your database (or choose only the tables you need) and give the Model Namespace a name, which I chose SampleAppModel.

In an alternate case, if you created the EDMX file already, then you would just update your model from the database to consider the new Identity Framework 2.0 tables which were added in Step 5.

Figure 2.3 - Add Database Objects to Model — Figure 2.2 — Add Database Objects to Model

Step 8 — Modify the Connection String in Web.Config in the Web Project

Once your EDMX file has been created, a new connection string will show up in App.Config (remember we created this in the data project). This connection string is shown below.

<add name="SampleAppDbContext" connectionString="metadata=res://*/DAL.SampleAppModel.csdl|res://*/DAL.SampleAppModel.ssdl|res://*/DAL.SampleAppModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDb)\v11.0;attachdbfilename=|DataDirectory|\SampleAppDb.mdf;initial catalog=SampleAppDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

This is how the data layer created in the data project will connect to the database. Since our startup project is set to SampleApp.Web, only the items in Web.config will be considered. Thus, we need to move this connection string over to Web.config. Once this is done, delete the App.Config file.

So now you have two connection strings in your Web.config file, one named SampleAppDbContext used by your data layer and the other named DefaultConnection, used by Identity Framework 2.0. Keep in mind that Identity Framework 2.0 uses a different kind of connection string than SampleAppDbContext. Make note of your database file name found in the SampleAppDbContext connection string and copy it.

Now, modify the connection string named DefaultConnection and give it a new name (I used IdentityDbContext) and have it match the database file name found in the SampleAppDbContext connection string. This will be a separate connection string used only by Identity Framework 2.0. Moreover, change the initial catalog to the name of your database, in my case SampleAppDb. The modified connection string is below along with the other connection string the data layer uses.

<add name="IdentityDbContext" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\SampleAppDb.mdf;Initial Catalog=SampleAppDb;Integrated Security=True" providerName="System.Data.SqlClient" />
<add name="SampleAppDbContext" connectionString="metadata=res://*/DAL.SampleAppModel.csdl|res://*/DAL.SampleAppModel.ssdl|res://*/DAL.SampleAppModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDb)\v11.0;attachdbfilename=|DataDirectory|\SampleAppDb.mdf;initial catalog=SampleAppDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Looking back, we changed the connection string name from DefaultConnection to IdentityDbContext, the name of the database file to SampleAppDb.mdf, and the initial catalog to SampleAppDb. Pay attention to the |DataDirectory| shortcut as this could have an impact on the file location.

So to recap, we now have two connection strings. One is used for accessing our data context as we normally would, and the other is used for Identity Framework 2.0. Both of these connection strings are different in type, SampleAppDbContext uses the System.Data.EntityClient provider and IdentityDbContext uses the System.Data.SqlClient provider. This is why we need two connection strings.

Step 9 — Modify IdentityModels.cs to Use the Correct Connection String

Under the web project’s Models folder, open IdentityModels.cs. The unmodified contents of this file are below.

using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.AspNet.Identity.Owin;

namespace SampleApp.Web.Models
{
    // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
    public class ApplicationUser : IdentityUser
    {
        public string Hometown { get; set; }

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }
}

using System.Security.Claims;

using System.Threading.Tasks;

using Microsoft.AspNet.Identity;

using Microsoft.AspNet.Identity.EntityFramework;

using Microsoft.AspNet.Identity.Owin;

namespace SampleApp.Web.Models

{

// You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.

public class ApplicationUser : IdentityUser

{

public string Hometown { get; set; }

public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)

{

// Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType

var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);

// Add custom user claims here

return userIdentity;

}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

{

public ApplicationDbContext()

: base("DefaultConnection", throwIfV1Schema: false)

{

}

public static ApplicationDbContext Create()

{

return new ApplicationDbContext();

}

Now, change the ApplicationDbContext class constructor to use the new connection string for Identity Framework 2.0, in my case IdentityDbContext (under the call to the base class constructor). The updated file is shown below.

using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.AspNet.Identity.Owin;

namespace SampleApp.Web.Models
{
    // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
    public class ApplicationUser : IdentityUser
    {
        public string Hometown { get; set; }

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("IdentityDbContext", throwIfV1Schema: false)
        {
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }
}

using System.Security.Claims;

using System.Threading.Tasks;

using Microsoft.AspNet.Identity;

using Microsoft.AspNet.Identity.EntityFramework;

using Microsoft.AspNet.Identity.Owin;

namespace SampleApp.Web.Models

{

// You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.

public class ApplicationUser : IdentityUser

{

public string Hometown { get; set; }

public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)

{

// Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType

var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);

// Add custom user claims here

return userIdentity;

}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

{

public ApplicationDbContext()

: base("IdentityDbContext", throwIfV1Schema: false)

{

}

public static ApplicationDbContext Create()

{

return new ApplicationDbContext();

}

Finally, rebuild the application and run it. Register a new user, and you will see that everything worked just fine. Upon closer inspection of the AspNetUsers table, which is now in your database, you will find the new user added. At this point, you can delete the initial database file created automatically earlier in this post (aspnet-SampleApp.Web-20140515030919.mdf) as it’s no longer needed.

A Few Cautions

This solution certainly isn’t perfect, but it worked for me. I spent countless hours toying with this. I found that the schema has to be dbo for the Identity Framework 2.0 tables. You can use a different schema for your database tables, but upon changing the schema for the Identity Framework 2.0 tables, the application would crash and burn. There are other solutions out there on the web where this isn’t the case, but I found them to be too cryptic. I am a big fan of simplicity, so for now, if my Identity Framework 2.0 tables are using the dbo schema, then I really can’t sweat it.

Moreover, if you created a separate class library project for your data layer as I did in this post, don’t make the mistake of using or adding any connection strings to App.Config since that data will be ignored. Only Web.config in the Web project will be used, since it’s set as the startup project. It’s my understanding that there are ways for explicitly using App.Config, but here I don’t recommend it.

Conclusion

I hope this helped you integrate Identity Framework 2.0 with your existing database. I have nothing against Code First, but most projects in the wild revolve around an existing database so knowing how to set things up properly is paramount. I would love to hear from you in the remarks about your experience with this post, and any other experiences with Identity Framework 2.0 and Database First. I am always open to new ideas or tricks and believe that sharing them is a win-win for everyone in the .NET community.

Sources

A few web searches led me to solve this problem. I have listed my sources below. None of them completely solved the problem for me, and through trial and error I figured this out. But credit is given where credit is due, and if I didn’t get some information from these sources, I might have spent countless more hours working on a solution.

Update (2/12/15)

Introduction

Step 1 — Create the ASP.NET MVC 5 Application Using the SPA Template

Step 2 — Build and Run the Application

Step 3 — Inspect the Web.config File Connection String and Connect to Local Database

Step 4 — Generate SQL Scripts For Each Database Table

Step 5 – Connect to Desired Database and Run SQL Scripts

Step 6 — Create Second Project For EDMX/Data Model

Step 7 — Create ADO.NET Entity Data Model

Step 8 — Modify the Connection String in Web.Config in the Web Project

Step 9 — Modify IdentityModels.cs to Use the Correct Connection String

A Few Cautions

Conclusion

Sources

Further Reading

Share this: