Entity Framework Migrations custom SQL not working

Question

Why does this not work?

AddColumn("dbo.Bins", "Code", c => c.String());
//custom copy data from old BinCode column
Sql("UPDATE Bins SET Code = BinCode WHERE BinCode IS NOT NULL");
DropColumn("dbo.Bins", "BinCode");

Bins has Bin.BinCode with values set, when I run this inside of DbMigration I find that Bin.Code is NULL.

EDIT: I don't actually run Update-Database in the package manager console, but I execute from my unit of work source:

        Database.SetInitializer<eVendContext>(new MigrateDatabaseToLatestVersion<eVendContext, Configuration>());

Edit 2: Just to clarify, The database is successfully updating to the latest migration. Simple the data is not copied across from BinCode field to Code field when complete.

Edit 3: Here's the relevant output from Update-database -verbose:

ALTER TABLE [dbo].[Bins] ADD [Code] [nvarchar](max)
UPDATE Bins SET Bins.Code = BinCode WHERE BinCode IS NOT NULL
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Bins')
AND col_name(parent_object_id, parent_column_id) = 'BinCode';
IF @var0 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Bins] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [dbo].[Bins] DROP COLUMN [BinCode]

When I run this output from verbose on my database as a complete script, I get an error "Invalid column name 'Code'". But each statement in turn updates my database as I would expect.

Does this mean I need to perform this style of data transposition over multiple migrations or is there a way to let the migration know it has to perform each step in the migration separately?

No error or exception, the data is not being transposed from old column to new column. — Smithy, Jan 14 '16 at 16:34
Did you actually do `Update-Database` command in Package Manager console? — Francis Ducharme, Jan 14 '16 at 16:47
@FrancisDucharme See my edit, I've just rolled back and it doesn't work when executed manually in the PMC either... — Smithy, Jan 14 '16 at 16:51
Where are you running `Database.SetInitializer` from ? Global.asax ? Are you sure you didn't start pointing at another database and expecting changes in another one ? That happens to me sometimes when using LocalDB. — Francis Ducharme, Jan 14 '16 at 16:55
I'm running it from the constructor of my MainForm (WinForms) — Smithy, Jan 14 '16 at 16:57
@Smithy Then make sure the connection strings from both the project holding your models, context, migrations, etc. is the same as the Winform project. Assuming it's not packaged all into one project... — Francis Ducharme, Jan 14 '16 at 16:59
@Francis Ducharme The migration is completing successfully, simply the data is not being copied from BinCode to Code once complete? — Smithy, Jan 14 '16 at 17:00
@SteveGreene When I run the output from verbose manually in SQL it says Invalid column name "Code". If I execute each statement manually it updates my database as expected. Is this my problem? — Smithy, Jan 14 '16 at 17:22
Yes, they are executed in batch statements without a GO after each. There is a way to insert GO's, but I would either just generate a script to do the update or consider moving the part that populates the new column into the Seed() method. http://stackoverflow.com/questions/11548959/entity-framework-migrations-including-go-statement-only-in-script-output — Steve Greene, Jan 14 '16 at 17:36

score 3 · Answer 1 · answered Jan 14 '16 at 17:26

3

Try:

Sql("UPDATE Bins SET Code = BinCode WHERE BinCode IS NOT NULL", true);

I suspect the statement is perhaps executed after the Code column is dropped or before it's created. This makes sure it's executed outside the transaction used for the migration.

answered Jan 14 '16 at 17:26

Francis Ducharme

4,848
6
43
81

No, already tried this. I can see where you're coming from but I would also need to execute AddColumn as a suppressed transaction (which it doesn't have an overload for) – Smithy Jan 14 '16 at 17:31

score 1 · Accepted Answer · answered Jan 14 '16 at 17:53

Try something like this in your migration Seed() which will run after the column is added:

if (context.Bins.Any(b => b.Code == null && b.BinCode != null)
{
    context.Database.ExecuteSQLCommand("UPDATE Bins SET Code = BinCode WHERE BinCode IS NOT NULL");
}

score 0 · Answer 3 · answered Jan 14 '16 at 16:47

0

Try this:

Sql("UPDATE Bins SET Bins.Code = Bins.BinCode WHERE Bins.BinCode IS NOT NULL");

answered Jan 14 '16 at 16:47

Abhijit Annaldas

669
4
12

Doesn't work, seems the same as what I have to be fair. – Smithy Jan 14 '16 at 16:53

Entity Framework Migrations custom SQL not working

3 Answers3