Code First Migration fails because of a Default Value Constraint

Question

Have a problem changing the type of a a property on an entity, a Default constraint was created, but EF isn't removing it when altering the column, thus, update-database is failing.

The entity previously had a DateTime property.

public DateTime ImportDate { get; set; }

The migration for it contained this in it's Up() method

AddColumn("dbo.Table", "ImportDate", c => c.DateTime(nullable: false));

However, it also created a Default Constraint

ALTER TABLE [dbo].[Table] ADD  DEFAULT ('1900-01-01T00:00:00.000') FOR [ImportDate]

The ImportDate is being changed to a DateTimeOffset

public DateTimeOffset ImportDate { get; set; }

And the resulting migration has an AlterColumn

AlterColumn("dbo.Table", "ImportDate", c => c.DateTimeOffset(nullable: false, precision: 7));

However, this fails, because the Default constraint exists.

The only way I can think of is using Sql(...) with a DROP CONSTRAINT, however, the CONSTRAINT has a seemingly random name DF__Table__Import__5441852A so hardcoded SQL will be not work everywhere.

Thoughts?

Answer 1

You can create an extension and use it in the generated migration:

 internal static class MigrationExtensions
    {
        public static void DeleteDefaultContraint(this IDbMigration migration, string tableName, string colName, bool suppressTransaction = false)
        {
            var sql = new SqlOperation(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);", tableName, colName)) { SuppressTransaction = suppressTransaction };
            migration.AddOperation(sql);
        }
    }

And used like this

this.DeleteDefaultContraint("dbo.Table", "ImportDate");

From this post: Group by in LINQ

Answer 2

The better way is to solve the problem for ever.

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