如何在C#项目中连接免费远程MySQL数据库并适配Entity Framework？

Got it, let's break down exactly how to connect your C# project to that free remote MySQL database from freemysqlhosting.net and get Entity Framework set up to auto-create your database structure. I'll cover both EF Core (the modern, recommended version) and EF6 since they're the most common options folks use day-to-day.

Step 1: Install Required NuGet Packages #

First, you need to add MySQL-specific EF packages to your project.

For EF Core (ASP.NET Core / Console Apps) #

I recommend using the Pomelo.EntityFrameworkCore.MySql provider (it's more actively maintained than the official MySQL one):

# Package Manager Console
Install-Package Pomelo.EntityFrameworkCore.MySql
Install-Package Microsoft.EntityFrameworkCore.Tools

Or if you prefer the official Oracle provider:

Install-Package MySql.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Tools

For EF6 (Legacy .NET Framework Apps) #

Use the official MySQL EF6 provider:

Install-Package MySql.Data.EntityFramework

Step 2: Configure Your Connection String #

You'll need to plug in the server details you got from freemysqlhosting.net. Don't forget to enable SSL—most free remote MySQL services require it to connect.

For EF Core (ASP.NET Core) #

Add this to your appsettings.json:

"ConnectionStrings": {
  "RemoteMySqlDb": "Server=sql12.freemysqlhosting.net;Port=3306;Database=YOUR_DB_NAME;Uid=YOUR_USERNAME;Pwd=YOUR_PASSWORD;SslMode=Required;"
}

For console apps, you can hardcode this string directly in your DbContext (or use a config file—hardcoding is fine for testing).

For EF6 (.NET Framework) #

Add this to your App.config or Web.config inside the <connectionStrings> section:

<connectionStrings>
  <add name="RemoteMySqlDb" 
       connectionString="server=sql12.freemysqlhosting.net;port=3306;database=YOUR_DB_NAME;uid=YOUR_USERNAME;password=YOUR_PASSWORD;SslMode=Required;" 
       providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Step 3: Set Up Your Entity Framework DbContext #

The DbContext is EF's core class that manages your database connection and entity mappings.

For EF Core #

Create a DbContext class that references your connection string and entities:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    // Replace with your actual entity classes
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }

    // For console apps: Configure the connection directly here
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySql(
            "Server=sql12.freemysqlhosting.net;Port=3306;Database=YOUR_DB_NAME;Uid=YOUR_USERNAME;Pwd=YOUR_PASSWORD;SslMode=Required;",
            new MySqlServerVersion(new Version(8, 0, 28)) // Match your MySQL version (freemysqlhosting uses 8.x)
        );
    }

    // For ASP.NET Core: Use dependency injection (better practice)
    // In Program.cs, add this instead of the OnConfiguring method:
    // builder.Services.AddDbContext<AppDbContext>(options =>
    //     options.UseMySql(builder.Configuration.GetConnectionString("RemoteMySqlDb"), 
    //         new MySqlServerVersion(new Version(8, 0, 28))));
}

For EF6 #

Create a DbContext that uses your connection string and sets an initialization strategy:

using System.Data.Entity;

public class AppDbContext : DbContext
{
    public AppDbContext() : base("name=RemoteMySqlDb")
    {
        // Auto-create the database if it doesn't exist
        Database.SetInitializer(new CreateDatabaseIfNotExists<AppDbContext>());
        // Optional: Use this if you want to rebuild the db when your model changes
        // Database.SetInitializer(new DropCreateDatabaseIfModelChanges<AppDbContext>());
    }

    // Replace with your actual entity classes
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
}

Step 4: Auto-Create the Database & Tables #

Now let's get EF to generate the database structure automatically.

For EF Core #

You have two options here:

1. Quick & Simple: Use EnsureCreated
   This creates the database (if your user has permission) and all tables in one go—great for testing, but it doesn't support migrations later:

   // Call this when your app starts (e.g., in Program.cs for ASP.NET Core)
   using (var context = new AppDbContext())
   {
       context.Database.EnsureCreated();
   }
   

2. Recommended: Use Migrations
   Migrations let you update your database schema over time without losing data. Run these commands in Package Manager Console:

   # Create an initial migration
   Add-Migration InitialCreate
   # Apply the migration to the remote database
   Update-Database
   

For EF6 #

EF6 will automatically create the database and tables the first time you access the DbContext. For example:

// In your app's startup code
using (var context = new AppDbContext())
{
    // Access any DbSet to trigger initialization
    var test = context.Products.FirstOrDefault();
}

Important Notes to Avoid Headaches #

• Check User Permissions: Free MySQL services like freemysqlhosting might not let you create a database remotely. If EnsureCreated or migrations fail, log into their web panel and manually create the database first—EF will still handle creating the tables.
• IP Whitelisting: Some free services block connections from unknown IPs. If you get a connection timeout, check if freemysqlhosting has an IP whitelist setting in their dashboard and add your public IP.
• SSL is Mandatory: Always include SslMode=Required in your connection string—omitting this will almost certainly cause a connection failure.
• MySQL Version: Make sure the MySqlServerVersion you specify in EF Core matches the version freemysqlhosting uses (they typically run MySQL 8.x).

内容的提问来源于stack exchange，提问作者user9866595