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

by Maurycy Markowski, Raquel Soares De Almeida, 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 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 Azure.

Creating a MySQL database instance on Azure

1. Log in to the Azure 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 CONNECTIONSTRING 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 templatewith 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 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:

   [!code-xmlMain]

      1:  <entityFramework>

      2:    <defaultConnectionFactory

      3:        type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">

      4:      <parameters>

      5:        <parameter value="v11.0" />

      6:      </parameters>

      7:    </defaultConnectionFactory>

      8:    <providers>

      9:      <provider

     10:        invariantName="System.Data.SqlClient"

     11:        type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />

     12:    </providers>

     13:  </entityFramework>

3. Replace those configuration settings with the following, which will configure the Entity Framework to use the MySQL provider:

   [!code-xmlMain]

      1:  <entityFramework>

      2:    <providers>

      3:      <provider invariantName="MySql.Data.MySqlClient"

      4:        type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity"/> 

      5:    </providers>

      6:  </entityFramework>

      7:  <system.data>

      8:    <DbProviderFactories>

      9:      <remove invariant="MySql.Data.MySqlClient"></remove>

     10:      <add name="MySQL Data Provider"

     11:        invariant="MySql.Data.MySqlClient"

     12:        description=".Net Framework Data Provider for MySQL"

     13:        type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.7.2.0"/>

     14:    </DbProviderFactories>

     15:  </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 Azure (note that providerName value has also been changed from the original):

   [!code-xmlMain]

      1:  <connectionStrings>

      2:    <add name="DefaultConnection"

      3:      providerName="MySql.Data.MySqlClient"

      4:      connectionString="[Insert your ConnectionString from Azure here]"/>

      5:  </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:

   [!code-csharpMain]

      1:  using System.Data.Common;

      2:  using System.Data.Entity;

      3:  using System.Data.Entity.Migrations.History;

      4:   

      5:  namespace IdentityMySQLDemo

      6:  {

      7:    public class MySqlHistoryContext : HistoryContext

      8:    {

      9:      public MySqlHistoryContext(

     10:        DbConnection existingConnection,

     11:        string defaultSchema)

     12:      : base(existingConnection, defaultSchema)

     13:      {

     14:      }

     15:   

     16:      protected override void OnModelCreating(DbModelBuilder modelBuilder)

     17:      {

     18:        base.OnModelCreating(modelBuilder);

     19:        modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();

     20:        modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();

     21:      }

     22:    }

     23:  }

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:

   [!code-csharpMain]

      1:  using System.Data.Entity;

      2:   

      3:  namespace IdentityMySQLDemo

      4:  {

      5:    public class MySqlConfiguration : DbConfiguration

      6:    {

      7:      public MySqlConfiguration()

      8:      {

      9:        SetHistoryContext(

     10:        "MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));

     11:      }

     12:    }

     13:  }

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.

[!NOTE] 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:

   [!code-csharpMain]

      1:  using IdentityMySQLDemo.Models;

      2:  using System.Data.Entity;

      3:  using System.Data.Entity.Infrastructure;

      4:  using System.Linq;

      5:   

      6:  namespace IdentityMySQLDemo

      7:  {

      8:    public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>

      9:    {

     10:      public void InitializeDatabase(ApplicationDbContext context)

     11:      {

     12:        if (!context.Database.Exists())

     13:        {

     14:          // if database did not exist before - create it

     15:          context.Database.Create();

     16:        }

     17:        else

     18:        {

     19:          // query to check if MigrationHistory table is present in the database 

     20:          var migrationHistoryTableExists = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(

     21:            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'IdentityMySQLDatabase' AND table_name = '__MigrationHistory'");

     22:   

     23:          // if MigrationHistory table is not there (which is the case first time we run) - create it

     24:          if (migrationHistoryTableExists.FirstOrDefault() == 0)

     25:          {

     26:            context.Database.Delete();

     27:            context.Database.Create();

     28:          }

     29:        }

     30:      }

     31:    }

     32:  }

2. Open the IdentityModels.cs file for your project, which is located in the Models directory, and replace it???s contents with the following:

   [!code-csharpMain]

      1:  using Microsoft.AspNet.Identity.EntityFramework;

      2:  using System.Data.Entity;

      3:   

      4:  namespace IdentityMySQLDemo.Models

      5:  {

      6:    // You can add profile data for the user by adding more properties to your ApplicationUser

      7:    // class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.

      8:    public class ApplicationUser : IdentityUser

      9:    {

     10:    }

     11:   

     12:    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

     13:    {

     14:      static ApplicationDbContext()

     15:      {

     16:        Database.SetInitializer(new MySqlInitializer());

     17:      }

     18:   

     19:      public ApplicationDbContext()

     20:        : base("DefaultConnection")

     21:      {

     22:      }

     23:    }

     24:  }

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. ]

< THANKS ME>