Migrating from MS SQL to MySQL using .NET EF Core

Today, we’ll walk you through the process of migrating from MS SQL to MySQL using .NET Entity Framework (EF) Core. This guide will provide step-by-step instructions, complete with code examples, to help you navigate this process smoothly.

Step 1: Install the MySQL Database Provider

The first step in our migration process involves installing the MySQL database provider for EF Core. This can be done via NuGet, a popular package manager for .NET. Run the following command in your project’s root folder:

dotnet add package Pomelo.EntityFrameworkCore.MySql

This command installs the Pomelo.EntityFrameworkCore.MySql package, which is the EF Core database provider for MySQL.

Step 2: Add Connection String to App Settings

Next, we need to add the MySQL connection string to our application’s settings. This is typically done in the appsettings.json file. Here’s an example:

JSON

{
  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;database=mydatabase;user=myuser;password=mypassword;"
  }
}

Replace localhost, mydatabase, myuser, and mypassword with your MySQL server’s details.

Step 3: Update Your DbContext

If you have an existing DbContext for MS SQL, you’ll need to update it to use MySQL. Here’s an example of how you might do this:

C#

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    // Your DbSets here
}

In your Startup.cs, update the ConfigureServices method to use MySQL:

C#

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseMySql(Configuration.GetConnectionString("DefaultConnection"),
            ServerVersion.AutoDetect(Configuration.GetConnectionString("DefaultConnection"))));
}

This code tells EF Core to use MySQL with the connection string named “DefaultConnection” that we defined in appsettings.json.

Step 4: Run EF Core Migrations

Now, it’s time to create the MySQL database from your code. You can do this by running the following command in your project root folder:

dotnet ef database update

This command applies any pending migrations to the database, which will result in the creation or modification of the database schema.

Note: You probably will face compatibility issues like for varchar(max) which isn’t compatible with MySQL. You have to back up and delete all existing migrations and test the code by creating new migrations.

Updating Your Database

If you make changes to your models and need to update the database schema, you can create a new migration and apply it using these commands:

dotnet ef migrations add MigrationName
dotnet ef database update

or 

Add-Migration MigrationName
Update-Database

Replace MigrationName with a name for your migration. The dotnet ef migrations add command creates a new migration with the specified name, and the dotnet ef database update command applies the new migration to the database.

Conclusion

Migrating from MS SQL to MySQL using .NET EF Core might seem like a daunting task, but with these steps, you should be able to navigate the process smoothly. Remember to always back up your data before starting the migration process and test your application thoroughly after the migration is complete. Happy coding!

Please note: This blog post is intended as a guide and might need to be adjusted based on your specific application and database schema.