Can we run SQL script using code first migrations?

Can we run sql script using code first migrations?

I am new to code first and if I want to save my changes to a SQL script file before update-database command of migration, is it possible?

If possible then please provide steps to get it done. Also if script is generated then is it possible that I can run that script using migration?

First you need to create a migration.

Add-Migration RunSqlScript

Then in the generated migration file you can write your SQL.

// PLAIN SQL
Sql("UPDATE dbo.Table SET Created = GETDATE()");

// FROM FILE
var sqlFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Custom.sql"); 
Sql(File.ReadAllText(sqlFile));

Then you run

Update-Database

What I like to do is to embed the SQL script as a resource in the assembly and use the SqlResource method. I have tested this approach with Visual Studio 2017 15.5.6.

First you need to create a migration file:

1. In Visual Studio make sure to set as start up project the project where your DbContext is defined

2. In Visual Studio open the PMC: View -> Other Windows -> Package Manager Console

3. In PMC Set the default project to the project that holds the DbContext

4. If you have both EF core and EF 6.x installed:

   EntityFramework\Add-Migration RunSqlScript

5. If you have only EF 6.x Installed:

   Add-Migration RunSqlScript

Add a Sql Script in the migration folder (I name it with the same prefix as the migration file)

In the File properties window make sure the Build Action is "Embedded Resource" Note that we don't need to copy to the output folder as the sql script will be embedded in the assembly.

Update the Up method in the RunSqlScript migration

public override void Up()
{
    string sqlResName = typeof(RunSqlScript).Namespace  + ".201801310940543_RunSqlScript.sql";
    this.SqlResource(sqlResName );
}

I hope this helps

Like SQL, we have another method SqlFile. you can directly use that.

For .NET Core and EF Core you can do something like this in migrations

protected override void Up(MigrationBuilder migrationBuilder)
{
   var schema = "starter_core";
   migrationBuilder.Sql($"INSERT INTO [{schema}].[Roles] ([Name]) VALUES ('transporter')");
}