CODEWITHSUNDEEP
entity-frameworkstored-proceduresef-code-firstentity-framework-migrations
Krzysztof Morcinek
Krzysztof Morcinek

Reputation: 1021

Update stored procedure in Code First DbMigration

I have solution for Stored Procedures creation exactly like in this answer https://stackoverflow.com/a/15171900.

I am running

Sql(Properties.Resources.Create_sp_DoSomething);

in my Initial DbMigration.

My sql scripts have code to first drop existing SP and then create new updated SP. So whene I run 

Sql(Properties.Resources.Create_sp_DoSomething);

in new DbMigration, and logic inside SP is changed everything works fine.

The problem arise when I want to update Stored Procedure with columns (lets say IsActive) which were added to model in later commits, and I am updating without existing DB (so new DB is created). Then It fails with

Invalid column name 'IsActive'.

Any good solution to that other than removing all existing calls to

Sql(Properties.Resources.Create_sp_DoSomething);

and have it only in newest DbMigration.

Upvotes: 2

Views: 3655

Answers (1)

Steve Greene
Steve Greene

Reputation: 12304

Separate your stored procedures from your model creation by doing your stored procedure updates in the migration Seed() method which runs after all the migrations:

context.Database.ExecuteSqlCommand(sp_DoSomething);

Since this runs with every update-database, you will need to make the script idempotent by adding an existance check to the start of Create_sp_DoSomething:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DoSomething')
BEGIN
    DROP PROCEDURE sp_DoSomething
END

Upvotes: 4

Related Questions