Setting up ASP.NET Identity with EF Code-First

Back to: ASP.NET MVC Tutorial For Beginners and Professionals

Setting up ASP.NET Identity Database using EF Code-First Approach

In this article, I am going to discuss How to Set Up the ASP.NET Identity Database using the EF Code-First Approach with both New and Existing Databases. Please read our previous article where we discussed How to get started with ASP.NET Identity in new and Existing Projects.

Create a new ASP.NET Identity Database with our New Project

If you open the IdentityModels.cs class file which is present inside the Models Folder of your application, then you will see the following Database Context class.

As you can see in the above code, the ApplicationDbContext class is derived from the IdentityDbContext class which is responsible for interacting with the database. The base constructor of IdentityDbContext class takes the name of the connection string to use while interacting with the database. Here, you can see, we are passing the connection string name as DefaultConnection.

The connection string contains the Data Source, Database Name, Provider, and other parameters needed in order to establish the connection with the database. You can define the connection string with the name DefaultConnection in the Web.config file of your application as follows.

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=LAPTOP-ICA2LCQL\SQLEXPRESS; Initial Catalog=AspNetIdentityDB;Integrated Security=True" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

As shown in the above connection string, we are providing the Data Source as SQL Express (this can be the Server name, or IP Address where your SQL Server Database is Installed). We have also provided the database name AspNetIdentityDB. Here, I am using Windows Authentication, if you want to use SQL Server Authentication, then you need to specify the User ID and Password.

In order to create the AspNetIdentityDB database, we need to run the application, click the Register link, fill out the form to create a new account, and click on the Register button as shown in the below image in our AspNetIdentityWithNewProject application.

Once you click on the Register button, it will take some time to create the database. Now, refresh the Databases folder in SQL Server and you should see the AspNetIdentityDB database with the following tables as shown in the below image.

Use of the above Tables in ASP.NET Identity:

1. __MigrationHistory: The presence of this table tells us that it is using Entity Framework Code First Approach.
2. AspNetRoles: This table stores role information.
3. AspNetUserClaims: This table stores Claims information.
4. AspNetUserLogins: This table is for third-party authentication providers like Twitter, Google, Facebook, Microsoft, etc. Information about those logins will be stored in this table.
5. AspNetUserRoles: This is a mapping table that tells us which users are in which roles.
6. AspNetUsers: This table stores the registered users of our application.

Create a new ASP.NET Identity Database with our Existing Project

In our AspNetIdentityWithNewProject, the ASP.NET Identity Template is there, so you can run the application and register a new user, and behind the scene, the database is going to be created. But if you see our AspNetIdentityWithExistingProject, no such template is available i.e. Account Controller and Manage Controller classes are not available. Then how we will create the ASP.NET Identity database. So, first of all, add the following connection string in the web.config file of AspNetIdentityWithExistingProject.

<connectionStrings>
  <add name="DefaultConnection"
       connectionString="Data Source=LAPTOP-ICA2LCQL\SQLEXPRESS;Initial Catalog=AspNetIdentityDB2;Integrated Security=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

As you can see everything is the same in the connection string except the database name. Here, we are providing the database name AspNetIdentityDB2.

If you do not have the ASP.NET Identity Project Template, then you can still trigger the creation of a database by using the ApplicationDbContext class. So, modify the HomeController of your AspNetIdentityWithExistingProject as follows.

using AspNetIdentityWithExistingProject.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace AspNetIdentityWithExistingProject.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            //Create an Instance of the ApplicationDbContext class
            ApplicationDbContext ctx = new ApplicationDbContext();
            //Fetch all the users, this will create the database behind the scene
            List<ApplicationUser> users = ctx.Users.ToList();
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Now, run the application and then refresh the database and the new ASP.NET Identity Database should be created as shown in the below image with the name AspNetIdentityDB2.

Please check the ASP.NET Identity Architecture article for more information about the above tables. So, we have seen how to create a new ASP.NET Identity Database with both new and existing Projects.

Setting up ASP.NET Identity in an Existing Database

Let us first create a database called EmployeeDB with one table called Employee with some test data by executing the following SQL Statement.

-- Create EmployeeDB database
CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

-- Create Employee Table
CREATE TABLE Employee
(
  EmployeeID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  Gender VARCHAR(100),
  Salary INT,
  Dept VARCHAR(50)
)
GO

-- Populate some test data into the Employee table
INSERT INTO Employee VALUES('Pranaya', 'Male', 10000, 'IT' )
INSERT INTO Employee VALUES('Anurag', 'Male', 15000, 'HR' )
INSERT INTO Employee VALUES('Priyanka', 'Female', 22000, 'HR' )
INSERT INTO Employee VALUES('Sambit', 'Male', 20000, 'IT' )
INSERT INTO Employee VALUES('Preety', 'Female', 25000, 'IT' )
INSERT INTO Employee VALUES('Hina', 'Female', 20000, 'HR' )
GO

Now, we need to add the ASP.NET Identity tables to this EmployeeDB database. For this first, change the connection string in the web.config file in any of the projects as shown below.

<connectionStrings>
  <add name="DefaultConnection" 
       connectionString="Data Source=LAPTOP-ICA2LCQL\SQLEXPRESS;Initial Catalog=EmployeeDB;Integrated Security=True" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

With these changes in place, now run the application and register a new user if you are working with AspNetIdentityWithNewProject else run the application if you are working with AspNetIdentityWithExistingProject. Once you run the application, then check the EmployeeDB database, and this database now should include all the ASP.NET Identity tables including the Employee table as shown in the below image.

In the next article, I am going to discuss Customizing ASP.NET Identity Models with Examples. Here, in this article, I try to explain Setting up ASP.NET Identity with Entity Framework Code-First Approach. I hope you enjoy this Setting up ASP.NET Identity with EF Code-First Approach article.