Use Entity Framework to update column length and data in single migration

Question

I'm using Entity Framework with code first migrations. I need to increase the length of a VARCHAR(50) column to VARCHAR(100) and update all the records in that column by doubling the string. So "abc" turns into "abcabc" (except the values will be longer than three characters).

It would be nice to be able to do this in a single code first migration but I'm having trouble getting it working. I first tried using this code:

AlterColumn("dbo.SomeTable", "SomeField", c => c.String(maxLength: 100, unicode: false));

using (TheEntityContext ctx = new TheEntityContext())
{
    foreach (Entities.SomeTable st in ctx.SomeTables)
        st.SomeField = st.SomeField + st.SomeField;

    ctx.SaveChanges();
}

but I got this error:

The model backing the 'TheEntityContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

I thought gee that's weird. Maybe I can't use Entity Framework inside a code first migration? So I tried this code:

AlterColumn("dbo.SomeTable", "SomeField", c => c.String(maxLength: 100, unicode: false));

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    string sql = "UPDATE SomeTable SET SomeField = SomeField + '' + SomeField";
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.ExecuteNonQuery();
}

but I got this error:

String or binary data would be truncated.

Then I though is the ALTER TABLE statement to make the field longer not taking effect before the UPDATE statement runs? So I changed the UDPDATE statement to be a 50 character string and it ran fine. Running Update-Database -Verbose also indicates that it's not running the ALTER TABLE statement before the UPDATE statement.

So what's the deal here? Do I have to run the ALTER TABLE in one migration then the code to update the table in another?

Answer 1

The point is that EF executes migrations as part of a transaction.

You open a new transaction inside up, which is not necessary, simply use

AlterColumn("dbo.SomeTable", "SomeField", c => c.String(maxLength: 100, unicode: false));

Sql("UPDATE dbo.SomeTable SET SomeField = '' + SomeField + SomeField");

In this case the Sql() function would run in the same transaction context and the error should not appear.

EDIT: clarification on transaction context of Sql() function.