DbMigrator - Problems with existing migrations

[forsti83]

Hi!

We have migrated our application from EF 6.4.4 to EF Classic 7.1.34. We are using MS SQL Server as database. Usually the application user executes the pending migrations at application start. Internally the DbMigrator class is used for this in code:

var migrator = new DbMigrator(new Context.Configuration());
migrator.Update();

Since the migration to EF Classic we are facing problems with migrations of this type:

public override void Up()
{
	AddColumn("dbo.TableName", "ColumnName", c => c.Int(nullable: false));

	Sql("UPDATE dbo.TableName SET ColumnName = 123");
}

We are getting the exception that the column "ColumnName" is invalid at runtime, when the SQL-statement is executed. We can fix this problem with wrapping the SQL statement in an EXEC:

public override void Up()
{
	AddColumn("dbo.TableName", "ColumnName", c => c.Int(nullable: false));

	Sql("EXEC('UPDATE dbo.TableName SET ColumnName = 123')");
}

It seems that the DbMigrator is not finishing the batch after the AddColumn statement like EF 6.4.4 did. Is there any explanation for this or even a config option for getting the old behaviour?

[JonathanMagnan]

Hello @forsti83 ,

I believe disabling the option BatchMigration in your context constructor will make the job:

public BloggingContext() : base((My.ConnectionString))
{
	this.Configuration.BatchMigration.IsEnabled = false;
}

Let me know if that fixed it.

Best Regards,

Jon

[forsti83]

Hello @JonathanMagnan ,

that fixed it. Thank you very much! Has EF Classic another default value for this config setting than EF?

Best Regards,
Andreas

[JonathanMagnan]

Hello Andreas,

I'm not sure to understand you question.

If you ask if there is another config that could affect the migration, I believe that's the only one

[forsti83]

Hello Jon,

no, the question is, if you have a clue, why our migrations are working with EF 6.4.4 (without setting the BatchMigration config option to false), but are failing with EF Classic 7.1.34 (again without setting the BatchMigration config option to false)?

Thank you and best regards,
Andreas

[JonathanMagnan]

Oh,

It fail in EF Classic since by default, we batch command.

Which means, instead of executing for example 100 SQL command to create all your tables and index, perhaps only 1-2 commands are executed.

So when batching the migration, this part will be executed way faster.

However, a custom script like your make SQL fail unless it's in a execute method.

This is similar to creating first a table:

CREATE TABLE A
(
	ColumnInt INT
)

then executing the following SQL

ALTER TABLE dbo.A
ADD ColumnInt2 INT

UPDATE A
SET ColumnInt2 = 1

even if somewhat valid, you will get the same error.

However, you found a workaround to make it works with the EXEC.

When directly in SSMS, you can also use the "GO" workaround:

ALTER TABLE dbo.A
ADD ColumnInt2 INT

GO

UPDATE A
SET ColumnInt2 = 1

GO

Let me know if that explains correctly the current behavior.

[forsti83]

Hi Jon,

thank you very much for the detailed information!

Best regards,
Andreas