EF 4.3.1 Migration Exception - AlterColumn defaultValueSql creates same default constraint name for different tables

Question

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?

Answer 1

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

Answer 2

Ok, based on nemesv's answer (accepted), here's how I ended up fixing the problem for now, until a fix is officially issued:

internal class MyFixedSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AlterColumnOperation alterColumnOperation)
    {
        if (alterColumnOperation == null)
            throw new ApplicationException("alterColumnOperation != null");

        ColumnModel column = alterColumnOperation.Column;
        if ((column.DefaultValue != null) || !string.IsNullOrWhiteSpace(column.DefaultValueSql))
        {
            using (IndentedTextWriter writer = Writer())
            {
                writer.Write("ALTER TABLE ");
                writer.Write(this.Name(alterColumnOperation.Table));
                writer.Write(" ADD CONSTRAINT DF_");
                writer.Write(alterColumnOperation.Table + "_"); //   <== THIS IS THE LINE THAT FIXES THE PROBLEM
                writer.Write(column.Name);
                writer.Write(" DEFAULT ");
                writer.Write(column.DefaultValue != null ? base.Generate(column.DefaultValue) : column.DefaultValueSql);
                writer.Write(" FOR ");
                writer.Write(this.Quote(column.Name));
                this.Statement(writer);
            }
        }
        using (IndentedTextWriter writer2 = Writer())
        {
            writer2.Write("ALTER TABLE ");
            writer2.Write(this.Name(alterColumnOperation.Table));
            writer2.Write(" ALTER COLUMN ");
            writer2.Write(this.Quote(column.Name));
            writer2.Write(" ");
            writer2.Write(this.BuildColumnType(column));
            if (column.IsNullable.HasValue && !column.IsNullable.Value)
            {
                writer2.Write(" NOT NULL");
            }
            this.Statement(writer2);
        }
    }
}


internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;

        SetSqlGenerator("System.Data.SqlClient", new MyFixedSqlServerMigrationSqlGenerator());
    }
    ...
}

Answer 3

This solution tested in EF 6.1.3. most probably work on previous versions.

You can implement a custom sql generator class derived from SqlServerMigrationSqlGenerator from System.Data.Entity.SqlServer namespace:

using System.Data.Entity.Migrations.Model;
using System.Data.Entity.SqlServer;

namespace System.Data.Entity.Migrations.Sql{
    internal class FixedSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator {
        protected override void Generate(AlterColumnOperation alterColumnOperation){
            ColumnModel column = alterColumnOperation.Column;
            var sql = String.Format(@"DECLARE @ConstraintName varchar(1000);
            DECLARE @sql varchar(1000);
            SELECT @ConstraintName = name   FROM sys.default_constraints
                WHERE parent_object_id = object_id('{0}')
                AND col_name(parent_object_id, parent_column_id) = '{1}';
            IF(@ConstraintName is NOT Null)
                BEGIN
                set @sql='ALTER TABLE {0} DROP CONSTRAINT [' + @ConstraintName+ ']';
            exec(@sql);
            END", alterColumnOperation.Table, column.Name);
                this.Statement(sql);
            base.Generate(alterColumnOperation);
            return;
        }
        protected override void Generate(DropColumnOperation dropColumnOperation){
            var sql = String.Format(@"DECLARE @SQL varchar(1000)
                SET @SQL='ALTER TABLE {0} DROP CONSTRAINT [' + (SELECT name
                    FROM sys.default_constraints
                    WHERE parent_object_id = object_id('{0}')
                    AND col_name(parent_object_id, parent_column_id) = '{1}') + ']';
            PRINT @SQL;
                EXEC(@SQL); ", dropColumnOperation.Table, dropColumnOperation.Name);

                    this.Statement(sql);
            base.Generate(dropColumnOperation);
        }
    }
}

and set this configuration:

internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;

        SetSqlGenerator("System.Data.SqlClient", new FixedSqlServerMigrationSqlGenerator ());
    }
    ...
}