ASP.NET Identity Framework 2.0 with MySQL database

ASP.NET Identity Framework 2.0 is integrated with EntityFramework. The entity classes available are Users, Roles, Claims and Logins. The Identity database can be any store – SQL Server, MySQL or Oracle. This article provides guidance on how to integrate ASP.NET Identity 2.0 with a MySQL database.

SQL scripts for MySQL

MySQL has naming conventions which are very different from SQL Server. It uses snake cases for variable names. The script for creating the relevant tables is given below.

User Table

CREATE TABLE `aspnet_user` (
  `id` varchar(128) NOT NULL,
  `email` varchar(256) DEFAULT NULL,
  `email_confirmed` tinyint(1) NOT NULL,
  `password_hash` longtext,
  `security_stamp` longtext,
  `phone_number` longtext,
  `phone_number_confirmed` tinyint(1) NOT NULL,
  `two_factor_enabled` tinyint(1) NOT NULL,
  `lockout_end_date_utc` datetime DEFAULT NULL,
  `lockout_enabled` tinyint(1) NOT NULL,
  `access_failed_count` int(11) NOT NULL,
  `user_name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Role Table

CREATE TABLE `aspnet_role` (
  `id` varchar(128) NOT NULL,
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User Role Table

CREATE TABLE `aspnet_user_role` (
  `user_id` varchar(128) NOT NULL,
  `role_id` varchar(128) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_role` (`role_id`),
  CONSTRAINT `fk_user_role_user` FOREIGN KEY (`user_id`) REFERENCES `aspnet_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_role_role` FOREIGN KEY (`role_id`) REFERENCES `aspnet_role` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User Claim Table

CREATE TABLE `aspnet_user_claim` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(128) NOT NULL,
  `claim_type` longtext,
  `claim_value` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_user_claim_user` FOREIGN KEY (`user_id`) REFERENCES `aspnet_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User Login Table

CREATE TABLE `aspnet_user_login` (
  `login_provider` varchar(128) NOT NULL,
  `provider_key` varchar(128) NOT NULL,
  `user_id` varchar(128) NOT NULL,
  PRIMARY KEY (`login_provider`,`provider_key`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_user_login_user` FOREIGN KEY (`user_id`) REFERENCES `aspnet_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a schema by the name: membership_db. Run the above scripts.

Integrating Identity in ASP.NET MVC application

NuGet packages

In a blank ASP.NET MVC project, install the following NuGet packages:

  • EntityFramework
  • MySQL.Data
  • MySQL.Entity
  • ASP.NET Identity Core
  • ASP.NET Identity Extensions

Web.config

Add the following connection string to the web.config:

<connectionStrings>
    <add name="MembershipDbConnection" connectionString="Server=localhost;Port=3306;Database=membership_db;Uid=root;Pwd=root;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

DbContext

IdentityDbContext provides an out-of-the-box implementation for integrating with SQL Server. Since we are using MySQL, and we used different variable names above, we have to map the entities to the appropriate tables and the properties to the appropriate columns. The DbModelBuilder class has all the mapping methods that we need.

public class ApplicationDbContext : IdentityDbContext<IdentityUser>
{
    public ApplicationDbContext()
        : base("MembershipDbConnection")
    {
    }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Map Table
        modelBuilder.Entity<IdentityUser>().ToTable("aspnet_user");
        modelBuilder.Entity<IdentityUserLogin>().ToTable("aspnet_user_login");
        modelBuilder.Entity<IdentityUserClaim>().ToTable("aspnet_user_claim");
        modelBuilder.Entity<IdentityRole>().ToTable("aspnet_role");
        modelBuilder.Entity<IdentityUserRole>().ToTable("aspnet_user_role");

        // Map Columns
        modelBuilder.Entity<IdentityUser>().Property(u => u.Id).HasColumnName("id");
        modelBuilder.Entity<IdentityUser>().Property(u => u.Email).HasColumnName("email");
        modelBuilder.Entity<IdentityUser>().Property(u => u.EmailConfirmed).HasColumnName("email_confirmed");
        modelBuilder.Entity<IdentityUser>().Property(u => u.PasswordHash).HasColumnName("password_hash");
        modelBuilder.Entity<IdentityUser>().Property(u => u.SecurityStamp).HasColumnName("security_stamp");
        modelBuilder.Entity<IdentityUser>().Property(u => u.PhoneNumber).HasColumnName("phone_number");
        modelBuilder.Entity<IdentityUser>().Property(u => u.PhoneNumberConfirmed).HasColumnName("phone_number_confirmed");
        modelBuilder.Entity<IdentityUser>().Property(u => u.TwoFactorEnabled).HasColumnName("two_factor_enabled");
        modelBuilder.Entity<IdentityUser>().Property(u => u.LockoutEndDateUtc).HasColumnName("lockout_end_date_utc");
        modelBuilder.Entity<IdentityUser>().Property(u => u.LockoutEnabled).HasColumnName("lockout_enabled");
        modelBuilder.Entity<IdentityUser>().Property(u => u.AccessFailedCount).HasColumnName("access_failed_count");
        modelBuilder.Entity<IdentityUser>().Property(u => u.UserName).HasColumnName("user_name");

        modelBuilder.Entity<IdentityRole>().Property(u => u.Id).HasColumnName("id");
        modelBuilder.Entity<IdentityRole>().Property(u => u.Name).HasColumnName("name");

        modelBuilder.Entity<IdentityUserRole>().Property(u => u.UserId).HasColumnName("user_id");
        modelBuilder.Entity<IdentityUserRole>().Property(u => u.RoleId).HasColumnName("role_id");

        modelBuilder.Entity<IdentityUserClaim>().Property(u => u.Id).HasColumnName("id");
        modelBuilder.Entity<IdentityUserClaim>().Property(u => u.UserId).HasColumnName("user_id");
        modelBuilder.Entity<IdentityUserClaim>().Property(u => u.ClaimType).HasColumnName("claim_type");
        modelBuilder.Entity<IdentityUserClaim>().Property(u => u.ClaimValue).HasColumnName("claim_value");

        modelBuilder.Entity<IdentityUserLogin>().Property(u => u.LoginProvider).HasColumnName("login_provider");
        modelBuilder.Entity<IdentityUserLogin>().Property(u => u.LoginProvider).HasColumnName("provider_key");
        modelBuilder.Entity<IdentityUserLogin>().Property(u => u.LoginProvider).HasColumnName("user_id");
    }
}

Creating new users

UserManager provides several methods to perform database operations within the Identity framework. First, we create the DBContext. Next, the UserStore is created by passing in the entity classes. Finally, the UserManager is created from the store. To create a new user, pass the new user to the Create method of User manager.

var context = new ApplicationDbContext();
var userStore = new UserStore<IdentityUser, IdentityRole, string, IdentityUserLogin, IdentityUserRole, IdentityUserClaim>(context);
var userManager = new UserManager<IdentityUser, string>(userStore);

for (int i = 1; i < 10; i++)
{
    var user = new IdentityUser();
    user.UserName = string.Concat("user", i);
    var result = userManager.Create(user);
}

Hopefully, the above code saved some time for you.

Related Posts

Leave a Reply

Your email address will not be published.