Entity Framework updates local database, but update script gives an error when applied to production

Question

I have a local dev environment and database that I update automatically with Update-Database, and then apply the migrations to the production environment with scripts (produced with Update-Database -Script).

In my latest update, I added a new column, and then in the Up() method of the migration, I added the necessary code to populate the column. The migration then looked like this:

public partial class AddPaymentOptionToPledge : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.tblPledge", "PaymentOptionId", c => c.Int());

        Sql(@"UPDATE dbo.tblPledge 
                SET dbo.tblPledge.PaymentOptionId = (SELECT ID FROM dbo.tblPaymentOption
                    WHERE dbo.tblPaymentOption.UserID = dbo.tblPledge.UserID
                    AND dbo.tblPaymentOption.IsDefault = 1)
                WHERE dbo.tblPledge.PaymentOptionId IS NULL
                    AND EXISTS (SELECT ID FROM dbo.tblPaymentOption
                    WHERE dbo.tblPaymentOption.UserID = dbo.tblPledge.UserID
                    AND dbo.tblPaymentOption.IsDefault = 1)");
    }

    public override void Down()
    {
        DropColumn("dbo.tblPledge", "PaymentOptionId");
    }
}

I had no problem running Update-Database locally, but when it came to applying the migration to production, I got the error Invalid column name 'PaymentOptionId'. The script that EF produced (which I was applying) was this:

ALTER TABLE [dbo].[tblPledge] 
    ADD [PaymentOptionId] [int]

UPDATE dbo.tblPledge 
SET dbo.tblPledge.PaymentOptionId = (SELECT ID 
                                     FROM dbo.tblPaymentOption
                                     WHERE dbo.tblPaymentOption.UserID = dbo.tblPledge.UserID
                                       AND dbo.tblPaymentOption.IsDefault = 1)
WHERE dbo.tblPledge.PaymentOptionId IS NULL   --<ERROR HERE>--
  AND EXISTS (SELECT ID 
              FROM dbo.tblPaymentOption
              WHERE dbo.tblPaymentOption.UserID = dbo.tblPledge.UserID
                AND dbo.tblPaymentOption.IsDefault = 1)

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201611101848224_AddPaymentOptionToPledge', N'J127T.PaymentGateway.DataModel.MigrationConfiguration',  0x1F8B0800000000000400E...8C8297E790610000 , N'6.1.3-40302')

To get it to work, I had to split the script into two steps, first running ALTER TABLE. After that, the rest of the script ran fine.

My attraction to EF Code First is that it automates much of the db admin, but in this case I had to manually tweak the script to get it to work. Is this a common requirement with EF, or should I have written the update statement in a more robust way so that it wouldn't cause a problem when applied as part of a migration?

Let mes guess, your dev db has had this column before and then you dropped it. I notice that after running similar statements separately on any db, it's possible to run them as one batch afterwards in that particular db. After restarting the db server, running the batch throws the error again. Something with caching and syntax checking I don't fully grasp. — Gert Arnold, Nov 15 '16 at 21:23
Not in this case, @Gert. I am trying not to touch the dev db at all (or the production db for that matter), and just let EF make the changes to ensure everything stays in sync. EF created the column for the first time. — rudivonstaden, Nov 15 '16 at 22:07
I think the script might need a `GO` after each statement, depending on which tool you are using to run the SQL scripts. More info [in this post](http://stackoverflow.com/questions/17105716/adding-go-statements-to-entity-framework-migrations). — Diana, Nov 15 '16 at 23:03

Entity Framework updates local database, but update script gives an error when applied to production

0 Answers0