一种EF（EntityFramework） MySQL修改表名去掉dbo前缀的方法

问题的出现

某个项目使用EntityFramework框架做ORM，采用了Code First的开发方式，数据库是MySQL 8。当修改当中的数据表名，再迁移至Web项目关联的数据库时，出现因表带有dbo前缀的问题，从而导致迁移失败。

以下是我重现这个问题的过程：

修改表名

修改Signature实体为SysSignature，如：

    public class SignatureConfig : EntityTypeConfiguration<SignatureEntity>{public SignatureConfig(){ToTable("SysSignatures");HasKey(x => x.Id);Property(x => x.FileExtension).HasColumnType("varchar").HasMaxLength(32);Property(x => x.ServerFolder).HasColumnType("varchar").HasMaxLength(128).IsRequired();Property(x => x.ServerName).HasColumnType("varchar").HasMaxLength(128).IsRequired();Property(x => x.Order).IsOptional();Property(x => x.Size).IsOptional();Property(x => x.IsValid).IsOptional();Property(x => x.OriginName).HasColumnType("varchar").HasMaxLength(64);}}

执行迁移及数据库更新

Add-Migration UpdateSignatureEntity

所得Migration文件如下：

    using System;using System.Data.Entity.Migrations;public partial class UpdateSignatureEntity : DbMigration{public override void Up(){RenameTable(name: "dbo.Signatures", newName: "SysSignatures");}public override void Down(){RenameTable(name: "dbo.SysSignatures", newName: "Signatures");}}

显然，该语句带了SQL Server的schema名称dbo，这在MySQL中Update-Database是会报错的：

Update-Database

MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'yourmysqldb.dbo.signatures' doesn't exist在 MySql.Data.MySqlClient.MySqlStream.ReadPacket()在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)在 MySql.Data.MySqlClient.MySqlDataReader.NextResult()在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)在 MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()在 System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)在 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)在 System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)在 System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)在 System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)在 System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)在 System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)在 System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)在 System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)在 System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)在 System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)在 System.Data.Entity.Infrastructure.Design.Executor.Update.<>c__DisplayClass0_0.<.ctor>b__0()在 System.Data.Entity.Infrastructure.Design.Executor.OperationBase.Execute(Action action)
Table 'yourmysql.dbo.signatures' doesn't exist

问题分析

从错误来看，这是由schama名称dbo的出现导致的，这在SQL Server中是没有问题的，但MySQL里，如果给一个表加上dbo会导致识别不出这个表。

当然，如果数量不多，我们完全可以通过迁移文件中查找替换的方式处理掉，但要一劳永逸，还得从Configuration处考虑。那么既然这事情涉及代码生成，就和CodeGenerator有关，通过修改Configuration类的构造方法，适应MySQL，能很好地解决这个问题。

问题解决

以我的项目为例，需要修改代码生成器，涉及数据初始化配置的修改，我的类是DbConfiguration，修改其构造方法：

        public DbConfiguration(){...// 使用MySqlMigrationCodeGenerator解决表名修改问题。CodeGenerator = new MySql.Data.EntityFramework.MySqlMigrationCodeGenerator(); ...}

这时候Add-Migration，得到如下的迁移文件：

    using System;using System.Data.Entity.Migrations;public partial class UpdateSignatureEntity : DbMigration{public override void Up(){RenameTable(name: "Signatures", newName: "SysSignatures");}public override void Down(){RenameTable(name: "SysSignatures", newName: "Signatures");}}

执行Update-Database，结果正确。

PM> Update-Database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [202411080810041_UpdateSignatureEntity].
Applying explicit migration: 202411080810041_UpdateSignatureEntity.
Running Seed method.