"Invalid column name" with Entity Framework migrations

ef-migrations entity-framework-6 sql-server-2014

Question

I'm having trouble updating a database using a migration that creates a new column and gives it values. I have distilled the problem down to a single, straightforward instance.

Here is the example:

public class Model
{
    public int Id { get; set; }

    public int Col2 { get; set; }
}

This is the situation:

public class Context : DbContext
{
    public DbSet<Model> Models { get; set; }
}

I established a database, enabled migrations, and an initial migration first.

PM> Enable-Migrations
PM> Add-Migration -Name Initial
PM> Update-Database

I then developed my model:

public int Col3 { get; set; }

then started a fresh migration:

PM> Add-Migration -Name AddedCol3

See the call to Sql() for the modification I made to the migration to update the data in Col3:

public override void Up()
{
    AddColumn("dbo.Models", "Col3", c => c.Int(nullable: false));
    Sql("update dbo.Models set Col3 = Col2");
}

public override void Down()
{
   DropColumn("dbo.Models", "Col3");
}

With this migration, I update the database, and I get:

Msg 207, Level 16, State 1, Line 2 Invalid column name 'Col3'.

This is the produced script:

ALTER TABLE [dbo].[Models] ADD [Col3] [int] NOT NULL DEFAULT 0
update dbo.Models set Col3 = Col2
-- Removed update to migration history.

Evidently, edit table and update requests cannot be handled by SQL Server in a single batch.

I attempted to includeSQL("GO"); between, however this leads to the mistake:

The argument 'sql' cannot be null, empty or contain only white space.

whenever you attempt to update.

How can I do this using migrations. I need the database to be regularly updated by my Up() and Down() procedures.

1
2
6/12/2018 1:41:43 PM

Accepted Answer

The fix offered by @scsimon corresponds to the SQL-statement

Sql("EXEC('update dbo.Models set Col3 = Col2')");

both script

ALTER TABLE [dbo].[Models] ADD [Col3] [int] NOT NULL DEFAULT 0
EXEC('update dbo.Models set Col3 = Col2')

and is effective.

This approach is my preference since I believe it will be less susceptible to EF adjustments.

1
6/14/2018 3:12:25 PM

Popular Answer

I discovered a solution by including a fake SQL-statement:

Sql("select 1\nGO");

The script that results is:

ALTER TABLE [dbo].[Models] ADD [Col3] [int] NOT NULL DEFAULT 0
GO

select 1

update dbo.Models set Col3 = Col2

Observe that theGO statement has been automatically transferred to a different place!

Regardless, SQL Server has accepted the script and is presently applying the modifications as specified in the script.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow