How to Use AspNet.Identity core in My Sql database

Question

I am developing one application using asp dot net core 2 using MySql database.Please help me How can i use Asp Net Identity in MySqlDatabase.

Lactam

Though this is a very late comment, this video will walk you through how to make it work on asp net core 3 youtube.com/watch?v=X4I0DUw6C84 – Pile

Answer 1

I had to do this for a client. And I did in an application with ASP.NET Core 1.0, but for curiosity I also tried for an application in .NET Core 2.0.

What I did was first install the Entity Framework MySQL package from https://www.nuget.org/packages/Pomelo.EntityFrameworkCore.MySql/ using package manager console.

After that I changed in the startup.cs, in the method ConfigureServices, the option UseSqlServer to UseMySql, like the image below.

In my appsettings.json I have the MySQL connection named IdentityConnection like this:

{
    "ConnectionStrings": {
        "IdentityConnection": "Server=127.0.0.1;Database=identitycoredb;Uid=root;Pwd=1234;"
    },

To create the identity tables I executed the migration command in package manager console:

EntityFrameworkCore\Update-Database -Verbose

Fanchan

this works well with .Net Core 2.0 & Pomelo 2.0.1 versions. But if you move to Core 2.1 then you will see pomelo hasn't made yet 2.1.0 so there is a bug which blocks db migrations. – Ligurian

Thanks for saying that @MehmetKurtipek. I didn't know about it. – Peace

@MehmetKurtipek They have fixed the bug in the latest pre-release version (v2.1.0-rc1-final). – Impenetrable

@AndyFurniss thanks for the info, I have noticed that today! – Ligurian

running this in .Net Core 2.2, with newly created project fails, any suggestions? – Astraea

can you plz tell me how to get system stored procedure in MYSQL? – K2

Answer 2

EDIT: At this point, .Net Core 2.0 doesn't support Identity with MySql, in a near future it may be supported again.

__

You need to plug Entity Framework with MySQL with Pomelo's connection and Identity should work. Check this out -> https://damienbod.com/2016/08/26/asp-net-core-1-0-with-mysql-and-entity-framework-core/

Pellmell

It will create all the Migrations ie( it will create all tables like AspNetUsers, AspNetRoles, etc..) And Is this Lib support for Core 2.0 – Lactam

You're right. I did it with 1.3 and it works. But at this point 2.0 doesn't have support. I'm sorry to waste your time. – Pellmell

@Bellash looks it's working with MySql since 2.0.5. Check this blog post. retifrav.github.io/blog/2018/03/20/… – Pellmell

@Claudio : sir can you tell me how to use system stored procedure of identity server in mysql database? – K2

Answer 3

You Can create an Identity Database along with your MySQL Database and use the Identity database for your authorization

This is how I do it.

   //MySQL Database 
     services.AddDbContext<EFDbContext>(options =>
                options.UseSqlServer("Server = ; Database =MySQL  ; Trusted_Connection = True; MultipleActiveResultSets = true"));
//Identity Database               
     services.AddDbContext<EFIdentityDbContext>(options =>
                    options.UseSqlServer("Server = ; Database = Identity; Trusted_Connection = True; MultipleActiveResultSets = true"));

This should work fine along with your MySQL DB

public class EFIdentityDbContext : IdentityDbContext
{
    public EFIdentityDbContext(DbContextOptions<EFIdentityDbContext> options )
        :base (options)
    {

    }


}

Jonathonjonati

Answer 4

I am late to this answer but, this developer has put the whole solution nicely, in this repository.

https://github.com/jasonsturges/mysql-dotnet-core

Putting the relevant code blocks here.

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(127));
    builder.Entity<IdentityRole>(entity => entity.Property(m => m.ConcurrencyStamp).HasColumnType("varchar(256)"));

    builder.Entity<IdentityUserLogin<string>>(entity =>
    {
        entity.Property(m => m.LoginProvider).HasMaxLength(127);
        entity.Property(m => m.ProviderKey).HasMaxLength(127);
    });

    builder.Entity<IdentityUserRole<string>>(entity =>
    {
        entity.Property(m => m.UserId).HasMaxLength(127);
        entity.Property(m => m.RoleId).HasMaxLength(127);
    });

    builder.Entity<IdentityUserToken<string>>(entity =>
    {
        entity.Property(m => m.UserId).HasMaxLength(127);
        entity.Property(m => m.LoginProvider).HasMaxLength(127);
        entity.Property(m => m.Name).HasMaxLength(127);
    });
}

This is tested by me on a project i was working on at the time of posting this answer.

Encompass

essentially, it adds the items that the error claims as far as the my sql db is concerned. – Encompass