EF migration for changing data type of columns

Solution 1:

You have a default constraint on your column. You need to first drop the constraint, then alter your column.

public override void Up()
{
    Sql("ALTER TABLE dbo.Received DROP CONSTRAINT DF_Receiv_FromN__25869641");
    AlterColumn("dbo.Received", "FromNo", c => c.String());
    AlterColumn("dbo.Received", "ToNo", c => c.String());
    AlterColumn("dbo.Received", "TicketNo", c => c.String());
}

You will probably have to drop the default constraints on your other columns as well.

I've just seen Andrey's comment (I know - very late) and he is correct. So a more robust approach would be to use something like:

 DECLARE @con nvarchar(128)
 SELECT @con = name
 FROM sys.default_constraints
 WHERE parent_object_id = object_id('dbo.Received')
 AND col_name(parent_object_id, parent_column_id) = 'FromNo';
 IF @con IS NOT NULL
     EXECUTE('ALTER TABLE [dbo].[Received] DROP CONSTRAINT ' + @con)

I know this probably doesn't help the OP but hopefully it helps anyone else that comes across this issue.

Solution 2:

static internal class MigrationExtensions
{
    public static void DeleteDefaultConstraint(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);
    }
}

public override void Up()
{
    this.DeleteDefaultConstraint("dbo.Received", "FromNo");
    AlterColumn("dbo.Received", "FromNo", c => c.String());
    this.DeleteDefaultConstraint("dbo.Received", "ToNo");
    AlterColumn("dbo.Received", "ToNo", c => c.String());
    this.DeleteDefaultConstraint("dbo.Received", "TicketNo");
    AlterColumn("dbo.Received", "TicketNo", c => c.String());
}

Solution 3:

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