EF 4.3.1迁移例外 - AlterColumn defaultValueSql创建了不同的表相同的默认约束名 [英] EF 4.3.1 Migration Exception - AlterColumn defaultValueSql creates same default constraint name for different tables

问题描述

我有我创建使用OOB数据库初始化的数据块，而我使用code先用EF 4.3.1。

I have a DB that I created using the OOB database initializer, and I am using Code First with EF 4.3.1.

我想采取的Add-迁移cmdlet的新的IgnoreChanges标志的优势，这样我可以改变我的一些列，并添加一个默认的SQL值。从本质上讲，我的一些实体有一个名为DateLastUpdated列，这是我想设置默认为SQL EX pression GETDATE（）。

I wanted to take advantage of the new "IgnoreChanges" flag on the Add-Migration cmdlet, so that I can alter some of my columns and add a default SQL value. Essentially, some of my entities have a column named DateLastUpdated, which I would like to set the DEFAULT to the sql expression GETDATE().

我使用添加迁移InitialMigration -ignorechanges创建的InitialMigration，然后添加以下到向上（）和向下（）：

I created the InitialMigration using "add-migration InitialMigration -ignorechanges", and then I added the following to the Up() and Down():

public override void Up()
{
    AlterColumn("CustomerLocations", "DateLastUpdated", c => c.DateTime(defaultValueSql: "GETDATE()"));
    AlterColumn("UserReportTemplates", "DateLastUpdated", c => c.DateTime(defaultValueSql: "GETDATE()"));
    AlterColumn("Chains", "DateLastUpdated", c => c.DateTime(defaultValueSql: "GETDATE()"));
}

public override void Down()
{
    AlterColumn("CustomerLocations", "DateLastUpdated", c => c.DateTime());
    AlterColumn("UserReportTemplates", "DateLastUpdated", c => c.DateTime());
    AlterColumn("Chains", "DateLastUpdated", c => c.DateTime());
}

然后我试图运行更新，数据库-verbose，但我看到它正试图创建数据库在同一个名为default的约束，以及SQL抛出一个异常：

Then I tried running "Update-Database -verbose", but I see that it is trying to create the same named default constraint on the database, and SQL throws an exception:

Applying explicit migrations: [201203221856095_InitialMigration].
Applying explicit migration: 201203221856095_InitialMigration.
ALTER TABLE [CustomerLocations] ADD CONSTRAINT DF_DateLastUpdated DEFAULT GETDATE() FOR [DateLastUpdated]
ALTER TABLE [CustomerLocations] ALTER COLUMN [DateLastUpdated] [datetime]
ALTER TABLE [UserReportTemplates] ADD CONSTRAINT DF_DateLastUpdated DEFAULT GETDATE() FOR [DateLastUpdated]
System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'DF_DateLastUpdated' in the database.
Could not create constraint. See previous errors.
   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.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   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)
   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.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()
There is already an object named 'DF_DateLastUpdated' in the database.
Could not create constraint. See previous errors.

它看起来像EF正在创建通过附加东风_与列名的DEFAULT约束，而不是使用表的名称，使这个独特的表。这是一个已知的bug，还是我做错了什么吗？

It looks like EF is creating the DEFAULT constraint by appending "DF_" with the name of the column, but not using the name of the table, to make this unique to the table. Is this a known bug, or am I doing something wrong here?

推荐答案

现在看来，这是一个已知的bug：<一href="http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/b4ba0dd5-de0f-470e-af20-4bc09a1d8622">msdn论坛

It seems it's a known bug: msdn forums

安德鲁·Ĵ彼得斯微软（MSFT）的回答是：

Andrew J Peters Microsoft (MSFT) replied:

感谢您报告这一点。该问题将进行RTM。

Thanks for reporting this. The issue will be fixed for RTM.

一个可能的解决方法是先使该列可空，这   将prevent迁移距离产生额外的DEFAULT约束。   一旦该列被创建，那么它可以被改变回   非空的。

A possible workaround is to initially make the column nullable, which will prevent Migrations from generating the extra DEFAULT constraint. Once the column is created, then it can be altered back to non-nullable.

但它definitelly不固定EF 4.3.1。这里是源的相关部分：

But it's definitelly not fixed in EF 4.3.1. Here is relevant part of the source:

// Type: System.Data.Entity.Migrations.Sql.SqlServerMigrationSqlGenerator
// Assembly: EntityFramework, Version=4.3.1.0, 
// Culture=neutral, PublicKeyToken=b77a5c561934e089
namespace System.Data.Entity.Migrations.Sql
{
  public class SqlServerMigrationSqlGenerator : MigrationSqlGenerator
  {
     protected virtual void Generate(AlterColumnOperation alterColumnOperation)
     {
      //...
      writer.Write("ALTER TABLE ");
      writer.Write(this.Name(alterColumnOperation.Table));
      writer.Write(" ADD CONSTRAINT DF_");
      writer.Write(column.Name);
      writer.Write(" DEFAULT ");
      //...

所以EF不设法使约束名称唯一。

So EF doesn't try to make the constraint name unique.

您应该尝试解决办法，并报告为一个bug。

You should try the workaround and report it as a bug.

编辑： 我刚刚意识到上述生成方法虚拟因此，在最坏的情况下，你可以从<$ C继承$ C> SqlServerMigrationSqlGenerator 并解决SQL生成并将其设置为在Configuration.cs的SQL生成器：

I've just realized that above mentioned Generate method is virtual so in the worst case you can inherit from SqlServerMigrationSqlGenerator and fix the SQL generation and set it as the sql generator in Configuration.cs:

public Configuration()
{
    AutomaticMigrationsEnabled = true;
    SetSqlGenerator("System.Data.SqlClient", 
        new MyFixedSqlServerMigrationSqlGenerator());
}

编辑2：

我觉得最好的办法，直到它固定退回到原始的SQL语句：

I think the best thing to do until it fixed to fall back to raw SQL:

public override void Up()
{
    Sql(@"ALTER TABLE [CustomerLocations] ADD CONSTRAINT 
        DF_CustomerLocations_DateLastUpdated 
        DEFAULT GETDATE() FOR [DateLastUpdated]");
    Sql(@"ALTER TABLE [CustomerLocations] ALTER COLUMN 
        [DateLastUpdated] [datetime]");
    //...
}

这篇关于EF 4.3.1迁移例外 - AlterColumn defaultValueSql创建了不同的表相同的默认约束名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！