c# - Remove a migration with an empty down method or alter stored procedure from another migration

Question

I am working on a stored procedure that generates some tax data and stores that data into a separate table in the database. A while back I created an empty migration and added the following to the migration to add the stored procedure to the DB.

using Microsoft.EntityFrameworkCore.Migrations;

namespace Tax.Data.Migrations
{
    public partial class sp_Generate5498TaxData : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            var sp = @"USE [TAX_DB]
                GO
                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                CREATE PROCEDURE sp_GenerateTaxData

                @Year int
                AS
                BEGIN
                   -- A bunch of SQL that is the stored procedure

                END
                GO";

            migrationBuilder.Sql(sp);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {

        }
    }
}

Without thinking about it I added the migration without putting anything in the down method. Now, I need to alter the stored procedure and I am not sure how to do it so as to not cause other issues. My initial though was to remove the migration and add a new one but since the down method is empty would this cause any issues? Can I just write the following into the down method of the existing migration?

public override void Down()
    {
        this.Sql("DROP PROCEDURE sp_GenerateTaxData");
    }

My other thought was to create a new migration and add in my SQL that instead says ALTER PROCEDURE sp_GenerateTaxData but again I am not sure what the side effects of this would be. I have looked around and cannot find information on how to best do this because of my mistake of not setting the down method initially. Any suggestions on how I can remote the existing migration or add a new one that will update the SP?

question from:https://stackoverflow.com/questions/65889293/remove-a-migration-with-an-empty-down-method-or-alter-stored-procedure-from-anot

Answer 1

Adding a migration without a down method is not necessarily an error. It just means that if you revert the migration, what was done in the Up method won't be undone in the database when reverting it. In this case, removing the migration from the database won't actually drop the stored procedure from the database, since you haven't instructed it in your Down method.

Therefore, you could just write the code for the Down method now, instructing EF Core to generate the script to remove your stored procedure. Then, when un-applying the migration EF will remove the stored procedure from the DB. It shouldn't matter that you write the Down code later on.

What you could also do is to remove the migration files and then manually drop the stored procedure from all involved databases (different environments and all local machines of the members of your team, which is not practical... that's what migrations more or less come to solve). However, if migration has been only applied in your local db and maybe one staging environment, you could do it manually without much hassle.

Finally, you could also just add a new migration where in the Up() method and alter the procedure, as you suggested.

IMO, you don't need to worry that much. The only problem (in this scenario) of not having the Down() method is that you won't get the script to revert what was done in the Up() method automatically by EF (in this case, drop the stored procedure). It can be more problematic if the migration was changing the schema and there were also other migrations that had been applied afterwards.