Can we run SQL script using code first migrations?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

A bit late, but could be helpful.

You can create a static helper class as:

public static class SqlFileTrigger {    
  public static string GetRawSql(string fileName) {
    var baseDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Migrations"); // your default migrations folder
    var filePath = Path.Combine(baseDir, fileName);
    return File.ReadAllText(filePath);
  }
}

Then create your SQL files with similar naming convention followed by "{migrationName}_Up.sql" and "{migrationName}_Down.sql" like:

For example, assuming your created migration is named as "20230120230041_AddAuditingProperties.cs"

This way they will be grouped under your migration .cs file properly. (You can also create a separate folder for this but I usually prefer to keep them stacked so I can easily check if needed)

Then you can add "20230120230041_AddAuditingProperties_Up.sql" as well as the "20230120230041_AddAuditingProperties_Down.sql" (if down sql is needed)

Then for .NET EF:

  public partial class AddAuditingProperties : Migration
        {            
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                Sql(Utils.SqlFileTrigger.GetRawSql("20230120230041_AddAuditingProperties_Up.sql"));
            }
    
            
            protected override void Down(MigrationBuilder migrationBuilder)
            {
                Sql(
                    Utils.SqlFileTrigger.GetRawSql("20230120230041_AddAuditingProperties_Down.sql"));
            }
        }

For .NET Core EF-Core

public partial class AddAuditingProperties : Migration
    {            
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(Utils.SqlFileTrigger.GetRawSql("20230120230041_AddAuditingProperties_Up.sql"));
        }

        
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(
                Utils.SqlFileTrigger.GetRawSql("20230120230041_AddAuditingProperties_Down.sql"));
        }
    }

PS: As a side note, if you are planing them to be deployed and run on server automatically, make sure you right click those created .sql files, and set "Build Action" as "Content" and "Copy to Output Directory" as "Copy always" - I had problems before when they were not set.

Answer 6

1. You can add a new migration using Package Manager Console like this:

   add-migration add_defaultValues 
   

2. And then in the up method add the following command that contains the command you to execute:

   protected override void Up(MigrationBuilder migrationBuilder)
   {
   
       string Sql = @"GO
               INSERT [dbo].[TableName] ([Layer], [Environment], [Group], [Section], [Value], [Description], [LastChangeDate], [LoadType]) VALUES (N'ADMIN', N'TEST', N'PERFORMANCE', N'ALLOW_DB', N'true', NULL, CAST(N'2022-07-18T00:00:00.0000000' AS DateTime2), N'STARTUP')
               GO
               INSERT [dbo].[TableName] ([Layer], [Environment], [Group], [Section], [Value], [Description], [LastChangeDate], [LoadType]) VALUES (N'ADMIN', N'TEST', N'PERFORMANCE', N'ALLOW_FILE', N'false', NULL, CAST(N'2022-07-18T00:00:00.0000000' AS DateTime2), N'STARTUP')
               GO
               INSERT [dbo].[TableName] ([Layer], [Environment], [Group], [Section], [Value], [Description], [LastChangeDate], [LoadType]) VALUES (N'ADMIN', N'TEST', N'PERFORMANCE', N'ALLOW_MAIL', N'false', NULL, CAST(N'2022-07-18T00:00:00.0000000' AS DateTime2), N'STARTUP')
               GO";
   
       migrationBuilder.Sql(Sql);
   
   }