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

问题描述

我有一个我使用OOB数据库初始化程序创建的数据库，我正在使用EF 4.3.1的Code First。

我想利用Add-Migration cmdlet上的新IgnoreChanges标志，以便我可以修改我的一些列，并添加一个默认的SQL值。基本上，我的一些实体有一个名为DateLastUpdated的列，我想将DEFAULT设置为sql表达式GETDATE（）。

我使用add -migration InitialMigration -ignorechanges，然后我将以下内容添加到Up（）和Down（）：

  public override void Up（）
 {
 AlterColumn（CustomerLocations，DateLastUpdated，c => c.DateTime（defaultValueSql：GETDATE（）））; 
 AlterColumn（UserReportTemplates，DateLastUpdated，c => c.DateTime（defaultValueSql：GETDATE（）））; 
 AlterColumn（链，DateLastUpdated，c => c.DateTime（defaultValueSql：GETDATE（）））; 
} 
 
 public override void Down（）
 {
 AlterColumn（CustomerLocations，DateLastUpdated，c => c.DateTime（））; 
 AlterColumn（UserReportTemplates，DateLastUpdated，c => c.DateTime（））; 
 AlterColumn（链，DateLastUpdated，c => c.DateTime（））; 
} 
  

然后我尝试运行Update-Database -verbose，但是我看到它尝试在数据库上创建相同的命名默认约束，SQL会引发异常：

 应用显式迁移：[201203221856095_InitialMigration ]。 
应用显式迁移：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）：数据库中已经有一个名为DF_DateLastUpdated的对象。 
无法创建约束。查看以前的错误。 
在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，布尔breakConnection）
在System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，布尔breakConnection）
在System.Data System.Data.SqlClient.TdsParser.Run中的.SqlClient.TdsParser.ThrowExceptionAndWarning（）
（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj）
在System.Data.SqlClient.SqlCommand .RunExecuteNonQueryTds（String methodName，Boolean async）
在System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（DbAsyncResult结果，String methodName，Boolean sendToPipe）
在System.Data.SqlClient.SqlCommand.ExecuteNonQuery（）
在System.Data.Entity.Migrations.DbMigrator.ExecuteSql（DbTransaction事务，MigrationStatement migrationStatement）
在System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql（DbTransac 
在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操作，布尔降级）
在System.Data.Entity.Migrations.DbMigrator.ApplyMigration（ DbMigration迁移，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，St ring lastMigrationId）
在System.Data.Entity.Migrations.DbMigrator.Update（String targetMigration）
在System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update（String targetMigration）
 at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore（）
在System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run（）
已经有一个名为' DF_DateLastUpdated。 
无法创建约束。查看以前的错误。 
  

看起来EF正在通过将DF_添加为列的名称来创建DEFAULT约束，但是不使用表的名称，使这个表独一无二。这是一个已知的错误，还是我在这里做错事？

解决方案

似乎这是一个已知的错误： msdn论坛

Andrew J Peters Microsoft（MSFT）回答：

感谢您的报告。该问题将针对RTM进行修复。

可能的解决方法是最初使列为空，其中
将阻止迁移生成额外的DEFAULT约束。
创建列后，可以将其更改回
不可空。

但它是定义在EF 4.3.1中未修复。这是源的相关部分：

  //键入：System.Data.Entity.Migrations.Sql.SqlServerMigrationSqlGenerator 
 // Assembly：EntityFramework，Version = 4.3.1.0，
 // Culture = neutral，PublicKeyToken = b77a5c561934e089 
命名空间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不会试图使约束名称唯一

您应该尝试解决方法并将其报告为错误。

编辑：
I我刚刚意识到上面提到的生成方法是 virtual 所以在最坏的情况下你可以继承 SqlServerMigrationSqlGenerator 并修复SQL生成并将其设置为Configuration.cs中的sql生成器：

  public Configuration（）
 {
 AutomaticMigrationsEnabled = true; 
 SetSqlGenerator（System.Data.SqlClient，
 new MyFixedSqlServerMigrationSqlGenerator（））; 
} 
  

编辑2：

我认为最好的事情要做，直到它恢复原始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]）; 
 // ... 
} 
  

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

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().

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());
}

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.

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?

解决方案

It seems it's a known bug: msdn forums

Andrew J Peters Microsoft (MSFT) replied:

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

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.

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 ");
      //...

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

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

EDIT: 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());
}

EDIT 2:

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屋！