"Invalid column name" with Entity Framework migrations

Question

I'm having an issue with updating a database with a migration that adds a column to a table and assign values to it. I have reduced the issue to a very simple case.

Here's the model:

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

    public int Col2 { get; set; }
}

This is the context:

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

First, I enabled migrations, created an initial migration and create a database.

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

Then, I extended my model:

public int Col3 { get; set; }

and created a new migration:

PM> Add-Migration -Name AddedCol3

I modified that migration to update the values in Col3, see the call to Sql():

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

When I update the database with this migration, I get:

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

The generated script is:

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

Apparently, SQL Server cannot handle the alter table and update in one batch.

I tried to add SQL("GO"); in between, but this results in the error:

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

when trying to update.

How can I use migrations to achieve this. I want my Up() and Down() methods to consistently update the database.

You are correct, they have to be different batches. You can use `execute` [**like this**](https://stackoverflow.com/a/15741378/6167855) as one work around — S3S, Jun 12 '18 at 13:52

score 1 · Accepted Answer · answered Jun 14 '18 at 15:12

The work-around provided by @scsimon translates to the SQL-statement

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

and script

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

and works fine.

I prefer this method, as I expect it to be less fulnerable to EF modifications.

score 0 · Answer 2 · answered Jun 12 '18 at 13:41

I found a work-around by adding a dummy SQL-statement:

Sql("select 1\nGO");

The resulting script is:

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

select 1

update dbo.Models set Col3 = Col2

Note that the GO statement has automagically been moved to another location!

Anyway, the script is now accepted by SQL Server and the changes are applied the way they are itended.

"Invalid column name" with Entity Framework migrations

2 Answers2