EF Core 2.0 update database again, once all migrations run

Question

It's a simple task I think.

My requirement is to run one .sql file after all migrations runs successfully. it contains few alter statements. the system is in a way that I must have to run this Sql, there are no other way like I just update my entity.

I am using asp.net zero architecture.

Right now I am updating my migrations manually and adding this query's with

 migrationBuilder.Sql("");

but it's hard to maintain.

I have done some R&D on this topic but not found anything proper.

as I am following best practice of .net boilerplate structure, I would like to hear from boilerplate dev side too.

score 1 · Answer 1 · answered Jan 30 '18 at 18:26

1

You can implement the requirement by creating Stored Procedures or Scalar value function.

Create Empty Migration by running Add-Migration command in Package Manager Console.

Add the SQL query in Up method like the following way.

public partial class testQuery : Migration {
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"You query");
    }
}

Or

Add Stored Procedures folder and testSP.sql inside it. testSP.sql will have whole SP definition.

protected override void Up(MigrationBuilder migrationBuilder)
{
    var spPath = @"MyCompany.MyTemplate.EntityFrameworkCore\Migrations\Stored Procedures\testSP.sql";

    var spMigratorPath = Path.Combine("..", spPath);

    if (!File.Exists(spMigratorPath))
    {
        spMigratorPath = Path.Combine("..", "..", "..", "..", spPath);
    }

    migrationBuilder.Sql(File.ReadAllText(spMigratorPath));
}

Run Update-Database in Package Manager Console.

It will create the function or SP in DB.

Now You can call SP by using ExecuteSqlCommand or ExecuteSqlCommandAsync methods.

You can also refer this.

answered Jan 30 '18 at 18:26

Vivek Nuna

25,472
25
109
197

vivek that's what I am doing now, but what if someone removes all migrations and create InitialDB, I will lost my query. – Bharat Jan 31 '18 at 05:10
why do we need to remove migration? – Vivek Nuna Jan 31 '18 at 05:11
because in big application, every week there are at least 50-60 migrations, and in team of more then >8 members, it's hard to maintain this kind of fixes – Bharat Jan 31 '18 at 05:12
yes, but its developer's responsibility, not to delete them or do it carefully – Vivek Nuna Jan 31 '18 at 05:14
this is not a question at all, what I want is generic solution, and that's why I posted my question over here.. – Bharat Jan 31 '18 at 05:16

EF Core 2.0 update database again, once all migrations run

1 Answers1