Entity Framework updates local database, but update script gives an error when applied to production
<p>I have a local dev environment and database that I update automatically with <code>Update-Database</code>, and then apply the migrations to the production environment with scripts (produced with <code>Update-Database -Script</code>).</p>

<p>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:</p>

<pre class="lang-cs prettyprint-override"><code>public partial class AddPaymentOptionToPledge : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.tblPledge", "PaymentOptionId", c =&gt; 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");
    }
}
</code></pre>

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

<pre class="lang-sql prettyprint-override"><code>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   --&lt;ERROR HERE&gt;--
  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')
</code></pre>

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

<p>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?</p>