EF CodeFirst新增表执行update-database报@objname参数错误求解

问题复现场景 #

使用EF CodeFirst工作流时，完成实体类表定义后执行add-migration生成迁移文件，后续执行update-database命令同步到数据库时抛出异常，核心错误提示：

Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
错误编号：15248，状态：1，级别：11
完整报错栈如下：

update-database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [202206021223499_mig_database-upd].
Applying explicit migration: 202206021223499_mig_database-upd.
System.Data.SqlClient.SqlException (0x80131904): Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass2_0.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Infrastructure.Design.Executor.Update.<>c__DisplayClass0_0.<.ctor>b__0()
   at System.Data.Entity.Infrastructure.Design.Executor.OperationBase.Execute(Action action)
ClientConnectionId:a1b686bd-9354-48c4-97bd-c5184f85c2ba
Error Number:15248,State:1,Class:11

错误根因 #

该错误是SQL Server原生抛出的，触发场景为迁移生成的SQL调用系统存储过程sp_rename修改列、约束、索引名称时，传入的对象参数不合法：要么参数模糊无法唯一定位对象，要么声明要操作的COLUMN类型对象不存在。

排查与解决步骤 #

• 定位出错SQL
  执行带详细日志参数的更新命令，查看EF实际生成的所有SQL语句，找到执行失败前最后一条sp_rename调用，90%以上的该错误都来自这类语句：
  update-database -Verbose
• 检查sp_rename参数合法性
  对照数据库实际结构，检查定位到的sp_rename语句是否存在以下问题：
  • 重命名列时未使用表名.旧列名的完整格式，仅传入了列名，或新列名错误携带了表名前缀
  • 目标列、外键、索引已被手动删除/重命名，当前操作重复执行重命名逻辑
  • 传入的对象名和数据库实际对象名拼写、大小写不匹配（数据库开启区分大小写排序规则时会触发）
  • 表名、列名是SQL关键字或带特殊字符，未用方括号[]包裹，导致解析歧义
• 修正迁移文件
  打开报错对应的迁移文件（即日志中提到的202206021223499_mig_database-upd.cs）：
  • 若为重复操作、对象不存在的场景，直接删除冗余的RenameColumn/RenameIndex/DropForeignKey等无效操作代码
  • 若为名称格式错误，手动修正方法传入的表名、列名参数，确保重命名操作的源对象名格式为[架构名].[表名].[旧对象名]，新名称仅写对象本身名称，不带表前缀
• 解决模型快照不一致问题
  如果错误是EF自动生成的外键重命名逻辑触发，本质是EF模型快照和数据库实际结构不匹配，按以下步骤对齐：
  1. 执行add-migration EmptyMig生成一个空的迁移文件
  2. 执行update-database EmptyMig完成快照和数据库结构的对齐
  3. 删除刚才生成的空迁移文件，重新生成业务需要的迁移即可
• 兜底处理
  若无法快速定位具体错误点，先备份数据库，删除本次生成的错误迁移文件，删除__MigrationHistory表中对应本次迁移版本的记录，重新生成迁移后再执行即可。

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