entity-framework-4.3 - EF 4.3.1 迁移播种未按预期工作

Question

我正在使用 EF 4.3.1 迁移，并且我有 Configuration 类，其中包含以下代码:

internal sealed class Configuration : DbMigrationsConfiguration<DbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(PayByPhoneDbContext context)
    {
        context.Roles.AddOrUpdate(r => r.Name, new Role { Name = "A" }, new Role { Name = "B" });
        context.Administrators.AddOrUpdate(a => a.Email, new Administrator { Email = "a@a.com" Name = "A", Role = context.Roles.Local.SingleOrDefault(role => role.Name == "A") });
    }
}

现在，当我在数据库不存在时运行迁移命令(MSBuild 脚本的一部分)时，会创建表并按预期进行播种。但是，当我在没有任何迁移的现有数据库上运行 migrate 命令并且所有数据都已经播种时(当需要更新而不是插入时)我在运行 migrate 命令时遇到错误:

No pending explicit migrations.

Running Seed method.

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Administrators_Roles_RoleId". The conflict occurred in database "xxxDB", table "dbo.Roles", column 'Id'.

The statement has been terminated.

堆栈跟踪:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   --- End of inner exception stack trace ---
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at System.Data.Entity.Migrations.DbMigrator.SeedDatabase()
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.SeedDatabase()
   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()

运行 SQL Server profiler 发现执行时出现错误:

exec sp_executesql N'update [dbo].[Administrators]
set [RoleId] = @0
where ([Id] = @1)
',N'@0 int,@1 bigint',@0=0,@1=1

在 Seed 方法中使用外键播种数据的正确方法是什么？

Answer 1

AddOrUpdate 会将您未指定的列重置为 null(或在 int 的情况下为零)。

我的理解是，这种行为(顺便说一句很糟糕)不适用于它识别为键或自动增量的列，但显然它适用于您的情况。

我的建议是检查以确保数据库中不存在该角色，如果不存在则执行标准 add() 。

我为我的所有种子数据执行此操作，因为您永远不知道以后何时可以提供一个接口(interface)来更新您播种的数据，并且您不希望 Seed() 方法在这些情况下覆盖您的更改。

另请记住，如果您曾经选择使用 MigrateDatabaseToLatestVersion 数据库初始化程序，那么 Seed() 将在您的应用程序每次重新启动时触发(dll 部署、web.config 更新等)。

最好在 Seed() 方法中进行防御性编码。

更新:朱莉勒曼有一个很好的blog post关于这种行为。