Click here to Skip to main content
15,867,771 members
Articles / Programming Languages / C#
Tip/Trick

How to Use MySql Database with ASP.NET Identity

Rate me:
Please Sign up or sign in to vote.
4.00/5 (5 votes)
3 Mar 2018CPOL1 min read 20.9K   4   1
This tip describes how to use ASP.NET identity entity framework with mysql database.

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:

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

The full code looks like this:

C#
[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.

C#
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.

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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Freelancer
India India
I completed my engineering in 2010.. Then i am working as freelancer.

Comments and Discussions

 
QuestionCan you check it for asp.net core 3.1 and latest identity server Pin
Kamran Shahid12-Oct-20 0:47
Kamran Shahid12-Oct-20 0:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.