ASP.NET Identity 2.0 and MariaDB

…I finally made it: ASP.NET Identity 2.0 running with MariaDB! It was more difficult than I expected it to be. I’ve been using EntityFramework with MySQL successfully in my day-time job for over two years now. As I was starting a small toy project at home last week I decided to go the easy route: ASP.NET Web API 2 running in an OWIN application. That was the perfect opportunity for giving ASP.NET Identity 2.0 a shot – instead of rolling my own authentication framework.

I still believe that this choice isn’t bad at all, but I underestimated the cost of getting it to run in a heterogeneous environment (read: not everything is Microsoft technology).

Surprisingly (to me – but hey!) Microsoft is providing an example of how to use MySQL and EntityFramework 6 as a storage provider for ASP.NET Identity 2.0. Sadly the example isn’t working out of the box – at least not in my setup. One aspect is that when they wrote the tutorial the MySQL Connector/NET wasn’t supporting EF Code-first migrations as well as it does nowadays. The second issue was the primary keys in the ASP.NET Identity tables being too long for MySQL.

Here the quick walk-through:

Create a new class library project (if you’d like to separate your entity context from the application as I do) and install following Nuget packages into that new project:

Here’s already the first difference with Microsoft’s tutorial, we don’t need to touch the configuration file for anything else than to add a connection string. The Nuget is adding everything else for us.

Please take note of the last part of the connection string:

Allow User Variables=True;

In a few places the code generator creates very smart queries that make use of user variables. With this additional parameter we allow their usage.

The next step is to create two classes. The first will contain our custom ApplicationUser and the second one is our EF context.

As you can see in the ApplicationContext class we’re overriding OnModelCreating: this is the “fix”:

Without those lines the migration failed with the cryptic error “Specified key was too long; max key length is 767 bytes” when creating the index on role names.

Now you can enable the Code-First migrations and add the MySQL migrations code generator:

Finally add a migration and update the database:

If everything’s okay you should have a running ASP.NET Identity 2.0 database context:

ASP.NET identity 2.0 context DB with Code-First Migration on MySQL

ASP.NET identity 2.0 context DB with EF6 Code-First Migration on MySQL

Have fun with it! For me it’s running as a marvel!

Hey! Sharing's great for your karma ;)
Tweet about this on Twitter
Share on Facebook
0Pin on Pinterest
0Email this to someone

About Yannic

Yannic is a daytime freelance software developer, inventor and full-time geek. There's not much technical stuff he wouldn't like to crack open to see how they work. As a perfectionist most of the things he's getting into are completed in the most fashionable manner.