How to Use MySql Database with ASP.NET Identity

Introduction

I was working on a website which only uses MySql database. And when I tried to use code first approach with ASP.NET role based website, everything worked fine until I use "update-database -verbose".

You will get the same error if you tried to MySQL in ASP.NET core websites too.

Output Error:

Quote:

"Specified key was too long; max key length is 767 bytes Mysql error in Entity Framework 6".

I tried many things and used many tutorials, but nothing worked.

Using the Code

So to solve the above error with mysql, all you have to do is add the below code to your dbcontext:

[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]

The full code looks like this:

[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
            Configuration.ProxyCreationEnabled = false;
            Configuration.LazyLoadingEnabled = false;
        }

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

The above code will not work sometimes and definitely will not work in ASP.NET Core. so another method is trying to limit the primary key length. In other words, varchar is considered as 3 bytes. So when you try to create table of primary key varchar(256), it is exceeding the limit, i.e., 256*3=768 which exceeds the limit. So all we have to do is set the limit for the primary key length using MaxLength method.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            // Customize the ASP.NET Identity model and override the defaults if needed.
            // For example, you can rename the ASP.NET Identity table names and more.
            // Add your customizations after calling base.OnModelCreating(builder);

            builder.Entity<ApplicationUser>(entity => entity.Property
            		(p => p.Id).HasMaxLength(128));
            builder.Entity<ApplicationUser>(entity => entity.Property
           		 	(p => p.NormalizedEmail).HasMaxLength(128));
            builder.Entity<ApplicationUser>(entity => entity.Property
            		(p => p.NormalizedUserName).HasMaxLength(128));

            builder.Entity<IdentityRole>(entity => entity.Property
            		(p => p.Id).HasMaxLength(128));
            builder.Entity<IdentityRole>(entity => entity.Property
            		(p => p.NormalizedName).HasMaxLength(128));

            builder.Entity<IdentityUserToken<string>>(entity => entity.Property
            		(p => p.LoginProvider).HasMaxLength(128));
            builder.Entity<IdentityUserToken<string>>(entity => entity.Property
            		(p => p.UserId).HasMaxLength(128));
            builder.Entity<IdentityUserToken<string>>(entity => entity.Property
            		(p => p.Name).HasMaxLength(128));

            builder.Entity<IdentityUserRole<string>>(entity => entity.Property
            		(p => p.UserId).HasMaxLength(128));
            builder.Entity<IdentityUserRole<string>>(entity => entity.Property
            		(p => p.RoleId).HasMaxLength(128));


            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property
            		(p => p.LoginProvider).HasMaxLength(128));
            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property
            		(p => p.ProviderKey).HasMaxLength(128));
            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property
            		(p => p.UserId).HasMaxLength(128));

            builder.Entity<IdentityUserClaim<string>>(entity => entity.Property
            		(p => p.Id).HasMaxLength(128));
            builder.Entity<IdentityUserClaim<string>>(entity => entity.Property
            		(p => p.UserId).HasMaxLength(128));

            builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property
            		(p => p.Id).HasMaxLength(128));
            builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property
            		(p => p.RoleId).HasMaxLength(128));
        }
    }

The above code is part of the ASP.NET Core 2 web application.

Points of Interest

I used role based authentication system with the help of the following tutorial. This tutorial uses SQL Server.

• http://bitoftech.net/2015/01/21/asp-net-identity-2-with-asp-net-web-api-2-accounts-management/

You can also get the source code from the above blog. But if you only want to use Role Based authentication with MySql, then you can check the below link.

Github source code for Rolebased authentication can be found at:

• https://github.com/codemasterblackperl/AspNetOwinRoleAuthWithMySql