.NET 6使用EF Core更新Azure MySQL数据库时连接失败

现象 #

能通过SSMS正常连接Azure MySQL数据库，但执行dotnet ef database update命令时抛出连接错误，错误信息如下：

Entity Framework Core 6.0.15 initialized 'ApplicationDbContext' using provider 'MySql.EntityFrameworkCore:6.0.10+MySQL8.0.32' with options: None
MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
 ---> System.Net.Sockets.SocketException (10051): Próba przeprowadzenia operacji, wykonywanej przez gniazdo, na nieosiągalnej sieci.
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
   at System.Threading.Tasks.ValueTask.ValueTaskSourceAsTask.<>c.<.cctor>b__4_0(Object state)
--- End of stack trace from previous location ---
   at System.Net.Sockets.TcpClient.CompleteConnectAsync(Task task)
   at MySql.Data.MySqlClient.NativeDriver.<>c.<Open>b__40_0(Exception ex)
   at System.AggregateException.Handle(Func`2 predicate)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   at MySql.Data.Failover.FailoverManager.AttemptConnection(MySqlConnection connection, String originalConnectionString, String& connectionString, Boolean mySqlPoolManager)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at MySql.EntityFrameworkCore.Storage.Internal.MySQLDatabaseCreator.<>c__DisplayClass16_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at MySql.EntityFrameworkCore.Storage.Internal.MySQLExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation)
   at MySql.EntityFrameworkCore.Storage.Internal.MySQLDatabaseCreator.Exists(Boolean retryOnNotExists)
   at MySql.EntityFrameworkCore.Storage.Internal.MySQLDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to connect to any of the specified MySQL hosts.

配置信息 #

连接字符串 #

"con": "Data Source=tcp:amcia.database.windows.net,1433;Initial Catalog=amcia;User ID=gryTerAdmin@amcia;Password=<password>;Connection Timeout=30;" 

Program.cs数据库配置代码 #

builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
    IConfiguration configuration = serviceProvider.GetService<IConfiguration>();
    string connectionString = configuration.GetConnectionString("con");
    options.UseMySQL(connectionString, mySqlOptions => { });
});

已排查操作 #

• 关闭本地防火墙
• 将本机IP添加至Azure MySQL防火墙白名单
• 环境：.NET 6.0，依赖Pomelo.EntityFrameworkCore 6.0.0-rc.2和EntityFrameworkCore 6.0.15

解决方案 #

1. 修正连接字符串端口 #

Azure MySQL默认端口为3306（1433是SQL Server默认端口），将连接字符串中的端口改为3306：

"con": "Data Source=tcp:amcia.database.windows.net,3306;Initial Catalog=amcia;User ID=gryTerAdmin@amcia;Password=<password>;Connection Timeout=30;" 

2. 统一EF Core与Pomelo版本 #

Pomelo.EntityFrameworkCore 6.0.0-rc.2为预发布版本，和正式版EntityFrameworkCore 6.0.15存在兼容性问题。将Pomelo包升级至.NET 6兼容的正式版（如6.0.2）：

dotnet add package Pomelo.EntityFrameworkCore.MySql --version 6.0.2

3. 显式指定SSL模式 #

Azure MySQL强制要求SSL连接，可在连接字符串中明确配置：

"con": "Server=amcia.database.windows.net;Port=3306;Database=amcia;Uid=gryTerAdmin@amcia;Pwd=<password>;SslMode=Required;" 

4. 验证命令行环境配置读取 #

确保执行dotnet ef命令时处于项目根目录，且appsettings.json（或对应环境配置文件）中的连接字符串已正确更新，避免命令行工具读取到旧配置。

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