Adding Entity Framework to a .Net Project Using MySql with Multiple Databases

October 25, 2024

I’ve previously written about adding Entity Framework to an existing project. In this post, I’m going to cover specifically adding EF using a MySql database, and also, how to deal with more than a single DB on the server.

Notes

Multiple Databases

It’s not uncommon for a single MySql instance to have more than a single DB. However, Entity Framework requires that you connect to a specific database. There’s a few ways around this, but in this post we’ll be solving it using multiple DbContexts.

Pomelo

Since MySql isn’t a Microsoft technology, there isn’t a MS NuGet library to deal with it; instead, we’ll be using this library.

For the purpose of this article, I’ll assume that you have your EF code in a separate project; if you haven’t, the code should still work fine.

Implementation

Let’s start by installing the EF Core NuGet libraries into the project that you wish to manage the data:

Install-Package Pomelo.EntityFrameworkCore.MySql

Install-Package Microsoft.EntityFrameworkCore

Now that we have that, we can create the DbContexts - each DbContext should be scoped to a given DB; for example:

public class UserDbContext : DbContext
{    
    public DbSet<User> Users { get; set; }

    public UserDbContext(DbContextOptions<UserDbContext> options)
           : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.ToTable("user", "users");

            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id)
                .HasColumnName("Id")
                .IsRequired()
                .ValueGeneratedOnAdd();
        }
    }
}

This example DbContext links to a database called users, with a single table called users.

Because this is being referenced from a library that isn’t the main project, I’ve created a helper method inside that project:

public static void AddUserDbContext(this IServiceCollection services, string connectionString)
{
    services.AddDbContext<UserDbContext>(
        dbContextOptions => dbContextOptions
            .UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
            // The following three options help with debugging, but should
            // be changed or removed for production.
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors()
    );

}

This can then be called from the main project (or even the same project if you choose to not separate):

builder.Services.AddUserDbContext(userConnectionString);
builder.Services.AddSalesDbContext(salesConnectionString);

Connection String

Just for completeness, this is what the appsettings.json might look like (obviously not suggesting that’s where you store the connection string, but for the purpose of illustration):

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MySqlUserConnection": "Server=my-server.mysql.database.azure.com;Database=users;Uid=mysqluser;Pwd=1234;",
    "MySqlSalesConnection": "Server=my-server.mysql.database.azure.com;Database=sales;Uid=mysqlsalesuser;Pwd=12342;"
  }
}

Conclusion

In this article, we’ve seen how we can add Entity Framework to an existing project, and how to connect to multiple databases using multiple DbContext instances.