Language

ASP.NET Identity: Using MySQL Storage with an EntityFramework MySQL Provider (C#)

By Raquel Soares De Almeida, Maurycy Markowski and Robert McMurray|

This tutorial shows you how to replace the default data storage mechanism for ASP.NET Identity with EntityFramework (SQL client provider) with a MySQL provider.

The following topics will be covered in this tutorial:

  • Creating a MySQL database on Windows Azure
  • Creating an MVC application using Visual Studio 2013 MVC template
  • Configuring EntityFramework to work with a MySQL database provider
  • Running the application to verify the results

At the end of this tutorial, you will have an MVC application with the ASP.NET Identity store that is using a MySQL database that is hosted in Windows Azure.

Creating a MySQL database instance on Windows Azure

  1. Log in to the Windows Azure Management Portal.
  2. Click NEW at the bottom of the page, and then select STORE:


  3. In the Choose and Add-on wizard, select ClearDB MySQL Database, and then click the Next arrow at the bottom of the frame:

    [Click the following image to expand it.]

  4. Keep the default Free plan, change the NAME to IdentityMySQLDatabase, select the region that is nearest to you, and then click the Next arrow at the bottom of the frame:

    [Click the following image to expand it.]

  5. Click the PURCHASE checkmark to complete the database creation.

    [Click the following image to expand it.]

  6. After your database has been created, you can manage it from the ADD-ONS tab in the management portal. To retrieve the connection information for your database, click CONNECTION INFO at the bottom of the page:

    [Click the following image to expand it.]

  7. Copy the connection string by clicking on the copy button by the CONECTIONSTRING field and save it; you will use this information later in this tutorial for your MVC application:

    [Click the following image to expand it.]

Creating an MVC application project

To complete the steps in this section of the tutorial, you will first need to install Visual Studio Express 2013 for Web or Visual Studio 2013. Once Visual Studio has been installed, use the following steps to create a new MVC application project:

  1. Open Visual Studio 2103.
  2. Click New Project from the Start page, or you can click the File menu and then New Project:

    [Click the following image to expand it.]

  3. When the New Project dialog box is displayed, expand Visual C# in the list of templates, then click Web, and select ASP.NET Web Application. Name your project IdentityMySQLDemo and then click OK:

    [Click the following image to expand it.]

  4. In the New ASP.NET Project dialog, select the MVC template with the default options; this will configure Individual User Accounts as the authentication method. Click OK:

    [Click the following image to expand it.]

Configure EntityFramework to work with a MySQL database

Update the Entity Framework assembly for your project

The MVC application that was created from the Visual Studio 2013 template contains a reference to the EntityFramework 6.0.0 package, but there have been updates to to that assembly since its release which contain significant performance improvements. In order to use these latest updates in your application, use the following steps.

  1. Open your MVC project in Visual Studio 2013.
  2. Click TOOLS, then click Library Package Manager, and then click Package Manager Console:

    [Click the following image to expand it.]

  3. The Package Manager Console will appear in the bottom section of Visual Studio. Type "Update-Package EntityFramework" and press Enter:

    [Click the following image to expand it.]

Install the MySQL provider for EntityFramework

In order for EntityFramework to connect to MySQL database, you need to install a MySQL provider. To do so, open the Package Manager Console and type "Install-Package MySql.Data.Entity -Pre", and then press Enter.

Note: This is a pre-release version of the assembly, and as such it may contain bugs. You should not use a pre-release version of the provider in production.

[Click the following image to expand it.]

Making project configuration changes to the Web.config file for your application

In this section you will configure the Entity Framework to use the MySQL provider that you just installed, register the MySQL provider factory, and add your connection string from Windows Azure.

Note: The following examples contain a specific assembly version for MySql.Data.dll. If the assembly version changes, you will need to modify the appropriate configuration settings with the correct version.

  1. Open the Web.config file for your project in Visual Studio 2013.
  2. Locate the following configuration settings, which define the default database provider and factory for the Entity Framework:
    <entityFramework>
      <defaultConnectionFactory
          type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
        <parameters>
          <parameter value="v11.0" />
        </parameters>
      </defaultConnectionFactory>
      <providers>
        <provider
          invariantName="System.Data.SqlClient"
          type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      </providers>
    </entityFramework>
  3. Replace those configuration settings with the following, which will configure the Entity Framework to use the MySQL provider:
    <entityFramework>
      <providers>
        <provider invariantName="MySql.Data.MySqlClient"
          type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity"/> 
      </providers>
    </entityFramework>
    <system.data>
      <DbProviderFactories>
        <remove invariant="MySql.Data.MySqlClient"></remove>
        <add name="MySQL Data Provider"
          invariant="MySql.Data.MySqlClient"
          description=".Net Framework Data Provider for MySQL"
          type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.7.2.0"/>
      </DbProviderFactories>
    </system.data>
  4. Locate the <connectionStrings> section and replace it with the following code, which will define the connection string for your MySQL database that is hosted on Windows Azure (note that providerName value has also been changed from the original):
    <connectionStrings>
      <add name="DefaultConnection"
        providerName="MySql.Data.MySqlClient"
        connectionString="[Insert your ConnectionString from Windows Azure here]"/>
    </connectionStrings>

Adding custom MigrationHistory context

Entity Framework Code First uses a MigrationHistory table to keep track of model changes and to ensure the consistency between the database schema and conceptual schema. However, this table does not work for MySQL by default because the primary key is too large. To remedy this situation, you will need to shrink the key size for that table. To do so, use the following steps:

  1. The schema information for this table is captured in a HistoryContext, which can be modified as any other DbContext. To do so, add a new class file named MySqlHistoryContext.cs to the project, and replace its contents with the following code:
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.Entity.Migrations.History;
     
    namespace IdentityMySQLDemo
    {
      public class MySqlHistoryContext : HistoryContext
      {
        public MySqlHistoryContext(
          DbConnection existingConnection,
          string defaultSchema)
        : base(existingConnection, defaultSchema)
        {
        }
     
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
          base.OnModelCreating(modelBuilder);
          modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
          modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
        }
      }
    }
  2. Next you will need to configure Entity Framework to use the modified HistoryContext, rather than default one. This can be done by leveraging code-based configuration features. To do so, add new class file named MySqlConfiguration.cs to your project and replace its contents with:
    using System.Data.Entity;
     
    namespace IdentityMySQLDemo
    {
      public class MySqlConfiguration : DbConfiguration
      {
        public MySqlConfiguration()
        {
          SetHistoryContext(
          "MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
        }
      }
    }

Creating a custom EntityFramework initializer for ApplicationDbContext

The MySQL provider that is featured in this tutorial does not currently support Entity Framework migrations, so you will need to use model initializers in order to connect to the database. Because this tutorial is using a MySQL instance on Azure, you will need need to create a custom Entity Framework initializer.

This step is not required if you are connecting to a SQL Server instance on Azure or if you are using a database that is hosted on premises.

To create a custom Entity Framework initializer for MySQL, use the following steps:

  1. Add a new class file named MySqlInitializer.cs to the project, and replace it's contents with the following code:
    using IdentityMySQLDemo.Models;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    
    namespace IdentityMySQLDemo
    {
      public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>
      {
        public void InitializeDatabase(ApplicationDbContext context)
        {
          if (!context.Database.Exists())
          {
            // if database did not exist before - create it
            context.Database.Create();
          }
          else
          {
            // query to check if MigrationHistory table is present in the database 
            var migrationHistoryTableExists = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
            string.Format(
              "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '__MigrationHistory'",
              "[Insert your database schema here - such as 'users']"));
    
            // if MigrationHistory table is not there (which is the case first time we run) - create it
            if (migrationHistoryTableExists.FirstOrDefault() == 0)
            {
              context.Database.Delete();
              context.Database.Create();
            }
          }
        }
      }
    }
  2. Open the IdentityModels.cs file for your project, which is located in the Models directory, and replace it's contents with the following:
    using Microsoft.AspNet.Identity.EntityFramework;
    using System.Data.Entity;
    
    namespace IdentityMySQLDemo.Models
    {
      // You can add profile data for the user by adding more properties to your ApplicationUser
      // class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
      public class ApplicationUser : IdentityUser
      {
      }
    
      public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
      {
        static ApplicationDbContext()
        {
          Database.SetInitializer(new MySqlInitializer());
        }
    
        public ApplicationDbContext()
          : base("DefaultConnection")
        {
        }
      }
    }

Running the application and verifying the database

Once you have completed the steps in the preceding sections, you should test your database. To do so, use the following steps:

  1. Press Ctrl + F5 to build and run the web application.
  2. Click the Register tab on the top of the page:

    [Click the following image to expand it.]

  3. Enter a new user name and password, and then click Register:

    [Click the following image to expand it.]

  4. At this point the ASP.NET Identity tables are created on the MySQL Database, and the user is registered and logged into the application:

    [Click the following image to expand it.]

Installing MySQL Workbench tool to verify the data

  1. Install the MySQL Workbench tool from the MySQL downloads page
  2. In the installation wizard: Feature Selection tab, select MySQL Workbench under applications section.
  3. Launch the app and add a new connection using the connection string data from the Azure MySQL database you created at the begging of this tutorial.
  4. After establishing the connection, inspect the ASP.NET Identity tables created on the IdentityMySQLDatabase.
  5. You will see that all ASP.NET Identity required tables are created as shown in the image below:

    [Click the following image to expand it.]

  6. Inspect the aspnetusers table for instance to check for the entries as you register new users.

    [Click the following image to expand it.]

Author Information

Raquel Soares De Almeida

Raquel Soares De Almeida – Raquel Almeida is a Software Developer Engineer in Test on the Azure Application Platform and Tools team where her focus is on ASP.NET and Web Stack Runtime.

Maurycy Markowski

Maurycy Markowski – Maurycy is a software design engineer in test at Microsoft.

Robert McMurray

Robert McMurray – Robert McMurray is a Programming Writer at Microsoft, and he has been with Microsoft since December of 1995. Robert worked in Microsoft Technical Support for IIS 1.0 through IIS 5.0, and as a Programming Writer for IIS 6.0 and IIS 7.0. Robert was a Program Manager on the IIS Product Team for IIS 7.0, IIS 7.5, and IIS 8.0, where he was responsible for IIS Express, URL Rewrite, and the Web Publishing technologies (FTP, WebDAV, FPSE). On the side Robert occasionally plays guitar and hammered dulcimer for a musical group that’s a mixture of acoustic jazz, bluegrass, and celtic influences (http://www.hammeron.com/).