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
– Smithy, Commented Jan 14, 2016 at 16:34
Did you actually do Update-Database command in Package Manager console? — Francis Ducharme
– Francis Ducharme, Commented Jan 14, 2016 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
– Smithy, Commented Jan 14, 2016 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
– Francis Ducharme, Commented Jan 14, 2016 at 16:55
I'm running it from the constructor of my MainForm (WinForms) — Smithy
– Smithy, Commented Jan 14, 2016 at 16:57

Francis Ducharme · Accepted Answer · 2016-01-14 17:26:56Z

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, 2016 at 17:26

Francis Ducharme

5,0438 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Smithy Over a year ago

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)

Steve Greene · Accepted Answer · 2016-01-14 17:53:01Z

1

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");
}

answered Jan 14, 2016 at 17:53

Steve Greene

12.4k2 gold badges36 silver badges56 bronze badges

Comments

Abhijit Annaldas · Accepted Answer · 2016-01-14 16:47:20Z

0

Try this:

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

answered Jan 14, 2016 at 16:47

Abhijit Annaldas

6674 silver badges12 bronze badges

1 Comment

Smithy Over a year ago

Doesn't work, seems the same as what I have to be fair.

Collectives™ on Stack Overflow

Entity Framework Migrations custom SQL not working

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related