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

Update (2/12/15)

Important: This article assumes you plan on using 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. Also if you plan on copying 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
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
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
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.

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
Once authenticated, look for the database which matched the name of the connection string.

Figure 1.7 - LocalDb Database
Figure 1.6 – LocalDb Database
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
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 in order to recreate these tables in our own database. This is done by creating SQL scripts for each table which can later be ran in a query on our own 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:

dbo.AspNetRoles:

dbo.AspNetUserClaims:

dbo.AspNetUserLogins:

dbo.AspNetUserRoles:

dbo.AspNetUsers:

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.

  1. dbo._MigrationHistory
  2. dbo.AspNetUsers
  3. dbo.AspNetRoles
  4. dbo.AspNetUserClaims
  5. dbo.AspNetUserLogins
  6. dbo.AspNetUserRoles

At this point if you need to add any relationships (E.g. foreign keys) from your own 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
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. Also, 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
The Entity Data Model Wizard will pop up asking what the model should contain. Select EF Designer from database.

Figure 2.1 - Entity Data Model Wizard
Figure 2.0 – Entity Data Model Wizard
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.2 - Connection Name to Store in App.Config
Figure 2.1 – Connection Name to Store in App.Config

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 of 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 take into account 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.

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 taken into consideration. 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. Also, 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.

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.

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.

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 own 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 Caveats

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 own 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 the time being if my Identity Framework 2.0 tables are using the dbo schema then I really can’t sweat it.

Also, 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 comments 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 of the information from these sources I might have spent countless more hours working on a solution.

  1. http://stackoverflow.com/questions/20668328/using-asp-net-identity-database-first-approch
  2. http://patrickdesjardins.com/blog/the-entity-type-applicationuser-is-not-part-of-the-model-for-the-current-context

Further Reading

For a great article to help nail home some good points of Identity Framework 2.0, visit this website.

Daniel Eagle

Currently residing in the Austin area, Daniel Eagle is an avid gamer, writer, technology/science enthusiast, software developer, and educator. He takes great pride in spreading knowledge and helping others.

More Posts - Website

Follow Me:
TwitterLinkedInYouTube