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.