CODEWITHSUNDEEP
c#entity-frameworkentity-framework-migrations
Smithy
Smithy

Reputation: 2190

Entity Framework Migrations custom SQL not working

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?

Upvotes: 2

Views: 1935

Answers (3)

Steve Greene
Steve Greene

Reputation: 12314

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

Upvotes: 1

Francis Ducharme
Francis Ducharme

Reputation: 4987

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.

Upvotes: 3

Abhijit Annaldas
Abhijit Annaldas

Reputation: 667

Try this:

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

Upvotes: 0

Related Questions