EF5代码首次迁移：“每个表中的列名称必须是唯一的”使用RenameColumn后出错 [英] EF5 Code First Migrations: "Column names in each table must be unique" error after using RenameColumn
问题描述
我有一个这样的课程：
public class TraversalZones
{
public int Low {get;组; }
public int High {get;组; }
}
然后我们意识到这些属性不是真正的名字，所以我们改变了他们：
public class TraversalZones
{
public int Left {get;组; }
public int Top {get;组; }
}
整个项目中的重命名正确重构，但我知道自动迁移在IDE中可以很容易地拿出这些显式的重命名，所以我首先检查一下，验证是否只有这个列重命名了：
update-database -f -script
果然只显示SQL降低和高并添加Left和Top。然后我添加了一个手动迁移：
add-migration RenameColumns_TraversalZones_LowHigh_LeftTop
并将生成的代码简化为：
public override void Up（）
{
RenameColumn（TraversalZones，Low，Left）;
RenameColumn（TraversalZones，High，Top）;
}
public override void Down（）
{
RenameColumn（TraversalZones，Left，Low）;
RenameColumn（TraversalZones，Top，High）;
}
然后我更新了db：
update-database -verbose
稍后我进行了几个迁移，我已经备份了生产并将其还原到本地数据库，以测试这个代码DB。该数据库中已经创建了TraversalZones表，其中旧列名称（低和高）我当然是通过更新它：
update-database -f -verbose
重命名命令出现在输出中 - 全部出现好：
EXECUTE sp_rename @objname = N'TraversalZones.Low'，@newname = N'Left'，@objtype = N 'COLUMN'
EXECUTE sp_rename @objname = N'TraversalZones.High'，@newname = N'Top'，@objtype = N'COLUMN'
[插入迁移历史记录]
然后，我运行了我的代码，并且错误地告诉我上次运行后数据库发生了变化，我应该运行 update-database ...。
所以我再次运行：
update-database -f -verbose
我现在坚持这个错误：
没有等待基于代码的迁移。应用自动迁移：
201212191601545_A自动迁移。
ALTER TABLE [dbo]。[TraversalZones] ADD [Left] [int] NOT NULL DEFAULT 0
System.Data.SqlClient.SqlException（0x80131904）：每个表中的列名必须唯一。表'dbo.TraversalZones'中的列名称Left不止一次。
在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，Boolean breakConnection，Action`1 wrapCloseInAction）
在System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，Boolean breakConnection，Action`1在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj，Boolean callerHasConnectionLock，Boolean asyncClose）中的
$ System.Data.SqlClient.TdsParser.TryRun中的
（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream， BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）
在System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds（String methodName，Boolean async，Int32 timeout）
在System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（TaskCompletionSource `1 completion，String methodName，Boolean sendToPipe，Int32 timeout，Boolean asyncWrite）
在System.Data.SqlClient.SqlCommand.ExecuteNonQuery（）
在System.Data.Entity.Migrations.DbMigrator.ExecuteSql（DbTransaction事务，MigrationStatement migrationStatement）
在System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql（DbTransaction事务，MigrationStatement migrationStatement）
在System.Data.Entity.Migrations.DbMigrator.ExecuteStatements（IEnumerable`1 migrationStatements）
在System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements（IEnumerable`1 migrationStatements）
在System.Data .Entity.Migrations.DbMigrator.ExecuteOperations（String migrationId，XDocument targetModel，IEnumerable`1 operations，Boolean downgrading，Boolean auto）
在System.Data.Entity.Migrations.DbMigrator.AutoMigrate（String migrationId，XDocument sourceModel，XDocument targetModel，Boolean降级）
在System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate（String migrationId，XDocument sourceModel，XDocument targetModel，Bool ean降级）
在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.Update（String targetMigration）
在System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update（ String targetMigration）
在System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore（）
在System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run（）
ClientConnectionId：c40408ee-def3-4553-a9fb-195366a05fff
每个表中的列名必须是唯一的。表'dbo.TraversalZones'中的列名Left不止一次。
所以，对于左列是否还需要将其放入此表格中，明确的迁移令人困惑我会假设RenameColumn会把事情保持在正确的状态，但它似乎没有。
当我把它试图做的一个 update-database -f -script ，如果手动迁移不在那里，我会尝试完成它所做的工作：
ALTER TABLE [dbo]。[TraversalZones] ADD [Left] [int] NOT NULL DEFAULT 0
ALTER TABLE [dbo]。[TraversalZones] ADD [Top] [int] NOT NULL DEFAULT 0
DECLARE @ var0 nvarchar（128）
SELECT @ var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id（N'dbo.TraversalZones'）
AND col_name（parent_object_id，parent_column_id）='Low';
IF @ var0 IS NOT NULL
EXECUTE（'ALTER TABLE [dbo]。[TraversalZones] DROP CONSTRAINT'+ @ var0）
ALTER TABLE [dbo]。[TraversalZones] DROP COLUMN [Low ]
DECLARE @ var1 nvarchar（128）
SELECT @ var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id（N'dbo.TraversalZones'）
AND col_name（parent_object_id，parent_column_id）='High';
IF @ var1 IS NOT NULL
EXECUTE（'ALTER TABLE [dbo]。[TraversalZones] DROP CONSTRAINT'+ @ var1）
ALTER TABLE [dbo]。[TraversalZones] DROP COLUMN [High ]
INSERT INTO [__MigrationHistory]（[MigrationId]，[Model]，[ProductVersion]）VALUES（'201212191639471_AutomaticMigration'，0x1F8B08000 ... 000，'5.0.0.net40'）
这似乎是Migrations中的错误。
解决方法显然是这样的：
update-database -f -script
你可以看到我的问题的结果。然后我从脚本中删除了一切，最后一行，并将其与DB对应，让Migrations知道：我们已经将该列重新命名，将其删除。
我可以现在继续执行这个数据库的副本，但是我担心的是，每次迁移都是针对生产副本（直到生产本身已被迁移的），这个问题将会继续存在。
更新
这是实际上是包括生产在内的所有其他实例的问题。肮脏的解决方案是在提交生成的版本和固定版本之后生成SQL脚本（ update-database -f -script ）。
一个稍微更清洁的解决方案是从脚本中获取SQL，添加手动迁移，并更改简单的内容：
public void Up（）
{
Sql（...从脚本中提取的SQL ...）;
}
这将确保运行此迁移的其他环境精确地按照您的意图进行。
测试这有点棘手，所以你可以这样做：
- 备份您的数据库，以防万一。
- 运行SQL。如果它正常工作，请将SQL设置为一边。
- 添加手动迁移，并在Up（）方法中清除所有内容。
- 运行update-database -f
- 现在通过添加
Sql来修改Up（）方法（...）;调用您拨出的SQL。
到目前为止，不运行SQL两次，其他环境得到该SQL的结果。
We're using Entity Framework 5.0 Code First and Automatic Migrations.
I had a class like so:
public class TraversalZones
{
public int Low { get; set; }
public int High { get; set; }
}
Then we realized these properties weren't really the right names, so we changed them:
public class TraversalZones
{
public int Left { get; set; }
public int Top { get; set; }
}
The rename refactored properly throughout the project, but I know Automatic Migrations aren't smart enough to pick up these explicit renames in the IDE, so I first checked to verify the only pending migration was this column rename:
update-database -f -script
Sure enough it just showed the SQL dropping Low and High and adding Left and Top. I then added a manual migration:
add-migration RenameColumns_TraversalZones_LowHigh_LeftTop
And fixed up the generated code to simply:
public override void Up()
{
RenameColumn("TraversalZones", "Low", "Left");
RenameColumn("TraversalZones", "High", "Top");
}
public override void Down()
{
RenameColumn("TraversalZones", "Left", "Low");
RenameColumn("TraversalZones", "Top", "High");
}
I then updated the db:
update-database -verbose
And got 2 column renames, just like I was expecting.
Several migrations later I backed up Production and Restored it to a local DB to test the code on this DB. This DB had the TraversalZones table already created in it, with the old column names (Low and High) I of course began by updating it:
update-database -f -verbose
And the rename commands appeared in the output - all appeared well:
EXECUTE sp_rename @objname = N'TraversalZones.Low', @newname = N'Left', @objtype = N'COLUMN'
EXECUTE sp_rename @objname = N'TraversalZones.High', @newname = N'Top', @objtype = N'COLUMN'
[Inserting migration history record]
I then ran my code, and it errored out telling me the database had changed since last run, and that I should run update-database... .
So I ran it again:
update-database -f -verbose
And am now stuck on this error:
No pending code-based migrations. Applying automatic migration:
201212191601545_AutomaticMigration.
ALTER TABLE [dbo].[TraversalZones] ADD [Left] [int] NOT NULL DEFAULT 0
System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'Left' in table 'dbo.TraversalZones' is specified more than once.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
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.Update(String targetMigration)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
ClientConnectionId:c40408ee-def3-4553-a9fb-195366a05fff
Column names in each table must be unique. Column name 'Left' in table 'dbo.TraversalZones' is specified more than once.
So, clearly Migrations is confused as to whether the column "Left" still needs to make it into this table; I would assume RenameColumn would leave things in the proper state, but it appears it has not.
When I dump what it's attempting to do to a update-database -f -script, I get it trying to do exactly what it would have done if the manual migration were not there:
ALTER TABLE [dbo].[TraversalZones] ADD [Left] [int] NOT NULL DEFAULT 0
ALTER TABLE [dbo].[TraversalZones] ADD [Top] [int] NOT NULL DEFAULT 0
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.TraversalZones')
AND col_name(parent_object_id, parent_column_id) = 'Low';
IF @var0 IS NOT NULL
EXECUTE('ALTER TABLE [dbo].[TraversalZones] DROP CONSTRAINT ' + @var0)
ALTER TABLE [dbo].[TraversalZones] DROP COLUMN [Low]
DECLARE @var1 nvarchar(128)
SELECT @var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.TraversalZones')
AND col_name(parent_object_id, parent_column_id) = 'High';
IF @var1 IS NOT NULL
EXECUTE('ALTER TABLE [dbo].[TraversalZones] DROP CONSTRAINT ' + @var1)
ALTER TABLE [dbo].[TraversalZones] DROP COLUMN [High]
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201212191639471_AutomaticMigration', 0x1F8B08000...000, '5.0.0.net40')
This appears to be a bug in Migrations.
The workaround, obviously, is this:
update-database -f -script
Which you can see the results of in my question. Then I tossed everything from the script but the last line, and ran that against the DB to let Migrations know: We already renamed that column, cut it out.
I can now proceed with this copy of the database, but I'm concerned every migration against copies of Production (until Production itself has been migrated) will keep having this issue. How can I resolve this properly without this workaround?
Update
This was in fact an issue in every other instance including Production. The dirty solution was to generate a SQL script (update-database -f -script), after committing the generated version and the fixed version.
A slightly cleaner solution is to take the SQL from the script, add a manual migration, and change the contents of Up to simply:
public void Up()
{
Sql("...That SQL you extracted from the script...");
}
This will ensure other environments running this migration do so precisely the way you intended.
Testing this is a bit tricky so you can approach it this way:
- Backup your db just in case.
- Run the SQL. If it works properly, set the SQL aside.
- Add the manual migration and wipe out everything in the Up() method. Leave it completely empty.
- Run update-database -f
- Now modify the Up() method by adding the
Sql("...");calling the SQL you set aside.
Now your db is up to date without running the SQL twice, and other environments get the results of that SQL.
这篇关于EF5代码首次迁移：“每个表中的列名称必须是唯一的”使用RenameColumn后出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
