.Net 5 Identity using MySQL in MVC projects

Setting up Identity to use MySQL or MariaDB requires just a few additional steps. Follow along in your Visual Studio to quickly get setup with a MVC login system that uses MySQL (or MariaDB) instead of SQL server as the backend.

[@DataVids]

A couple of good links to read as next steps (watch this video first as a pre-requisite and to get authentication done before doing anything with authorization, so you can get your initial project setup...) - Customize your identity: docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-6.0 Then, add either role based authorization: docs.microsoft.com/en-us/aspnet/core/security/authorization/roles?view=aspnetcore-6.0 or claims based authorization: docs.microsoft.com/en-us/aspnet/core/security/authorization/claims?view=aspnetcore-6.0

[@lightbringer4352]

Thank you again.

[@DataVids]

my pleasure! thank you so much!!

[@jaspercoleman3269]

Thanks for the video! I needed a way to do more of my DotNet development on my M1 Macs, and this completed the gap of what I was missing.

[@DataVids]

You're welcome!

[@MichaelFurmedge]

This is spooky, I was literally thinking about this yesterday and wondering if there was a cheaper option than SQL Server for making personal projects. Thanks for making the video :)

[@DataVids]

You know that's what got me onto the topic, a few times I have dug into this with the idea of saving money on personal projects. Bright minds think alike, @Michael Furmedge.

[@thecyberhelp1]

Thanks bro love from India

[@DaveNorthWest]

Cool! Thanks! This basically converted the entity to use MySQL rather than MSSQL. Thanks for making this great video! Btw it works with all the other packages being version 8.0.11 now and adding the pomelo.entityframworkcore.mysql version 8.0.2. Its almost year 2025 now as of posing this comment

[@lightbringer4352]

You saved my day, thank you so much

[@DataVids]

So glad I could help! Have a great day.

[@deepakdutta2879]

Can we do without _migration table and entity validation? I have old apps that need to be updated but I don't have migration table and the fails with error that cannot validate the model because no migrations history found.

[@MikeJones-gt8ke]

Thanks, this was exactly what I needed!

[@DataVids]

Best news today!

[@jemschaudhary5922]

Sorry one more request from me: (mysql) Most of the times we want to change the PK of aspnetusers table to email (for example), and then use it as FK in other tables. I remember the conflict of that PK FK changes in other asp.net identity generated tables (aspnetroles, aspnetclaims,...). Can you please provide a solution how to change the PK and handle FK constraint in all identity tables?

[@DataVids]

Hm, I might need a bit more information to assist on this one. However, the primary key on aspnetusers for you is 'Id' right? And is the reason you want to change that key because you want to make a complex key to ensure it is unique or for reporting? If this is the case I would think you could just add a constraint to keep it unique instead of changing the primary key. Or are you maybe trying to make an auto increment ID? The thing about identity is its going to generate that value for you. If its a foreign key constraint error, that is something different: that means that you might have data in a table that doesn't have a matching value in the table that is linked by foreign key. There are other foreign key errors, workbench usually avoids them if you use their 'sometimes functional' UI, but here is an interesting article: www.cockroachlabs.com/blog/common-foreign-key-mistakes/

[@jemschaudhary5922]

@@DataVids Thanks for amazing explanation. I has other tables which can use 'email' as FK. I can you use userid as FK in other tables to totally skip that confusion. But was looking for sweet solution for that issue. Thanks again!

[@jemschaudhary5922]

If you have not created any video on identity - 'Roles', Can you make one on covering that topic (with mysql db)? How to set up roles and how to works and any possible problems in the way? Thanks in advance!

[@DataVids]

Yes I will add identity-roles to the list, but it might be a while before I get to that one. Hopefully you are in dotnet 6 or 7, here is a interesting read: learn.microsoft.com/en-us/aspnet/core/security/authorization/roles?view=aspnetcore-7.0

[@jemschaudhary5922]

@@DataVids Thanks for that!

[@jemschaudhary5922]

Great video. Thanks! Are there any problems in future if using mysql database and asp.net 'identity system'? Or it works similar without any problems with sql or mysql after those steps above?

[@DataVids]

I think you are fine to use MySQL. The last time I did that I used Pomelo.EntityFrameworkCore.MySql as the provider.

[@jemschaudhary5922]

@@DataVids Okay! Thanks :)

[@Пек-б5б]

Thanks for the video!

[@DataVids]

You're welcome!

[@wcameros1]

Me has ayudado mucho, en mi proyecto. Gracias, Gracias...

[@DataVids]

Very glad to have helped! Thanks for watching

[@spiedey8]

Thanks for the info!

[@DataVids]

You bet!