Code First Migrations - Update-database -script command generated SQL script not working

Question

I have to created a database through Entity Framework 5 with the following model:

public class Post
{
        public int PostId { get; set; }
        [MaxLength(200)]
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
}

Then I have added new property in Post

public string Abstract { get; set; }

then I have run

Add-Migration AddPostAbstract

which created the following class in my Migrations folder, after that I have modified this file by adding one more SQL statement

//201308300714477_AddPostAbstract.cs
public override void Up()
{
                AddColumn("dbo.Posts", "Abstract", c => c.String());
                Sql("UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
}

public override void Down()
{
    DropColumn("dbo.Posts", "Abstract");
}

After that I have executed this command

Update-Database –Verbose

which updated my database and also returned this SQL query:

ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL

Now I have deleted Abstract column from table and tried to execute the above query manually in SQL at that time it show me following error.

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

My question is why this query is not executed in SQL even this query is generated through migration?

Or is there any way to run such multiple query through migration generated script.

Answer 1

The -Verbose output just shows a summary of statements. If you run your command manually in SQL Server Management Studio then you need a GO between the two statements:

ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
GO
UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL

The quick fix is to do something like:

• update-database -script
• then use Sql Server management Studio to do the following search and replace in the generated script:
  • find what: ^{:b*}{{INSERT|UPDATE|SELECT|DELETE}.+} (this finds any CRUD statements)
  • replace with: \1GO\n\1\2\n (keep the indents, and add GO before any CRUD statements)
  • Find options: Use regular expressions

But, note that -Verbose doesn't give you the output you want, you need the output from -Script, or you will be missing the inserted data for the __MigrationHistory history table which could cause your application to throw an error when it runs (see below for details).

Details

Your comment below about the information on the MSDN Code First Migrations page is interesting. The page actually states (under the section "Getting a SQL Script")

Run the Update-Database command but this time specify the –Script flag

If you do this you will see something like:

ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ( ...

The INSERT is important - this is how your EF in your application will know it is using the latest db version (and will therefore run instead of showing you an error). But, it is still missing that GO command. SQL server therefore tries to compile the 3 lines as a single batch and fails.

After adding the GO statements you need, you can still run this in a single transaction by surrounding it with:

BEGIN TRANSACTION;
BEGIN TRY
   --Your migration code, with GO statements
END TRY
BEGIN CATCH
  SELECT 
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
GO

If you are frustrated because you are generating large scripts, putting a GO at the end of any ALTER TABLE line is trivial with replace in SSMS, which would be something like the one at the top of this answer