How to run migration SQL script using Entity Framework Core

Question

I faced with an issue, where I can't reach the SQL script to apply the migration. Here is my migration code:

 public partial class AddSomethingMigration : Migration {     private const string MIGRATION_SQL_SCRIPT_FILE_NAME = @"Migrations\Scripts\20170710123314_AddSomethingMigration.sql";      protected override void Up(MigrationBuilder migrationBuilder)     {         string sql = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).FullName, MIGRATION_SQL_SCRIPT_FILE_NAME));         migrationBuilder.Sql(File.ReadAllText(sql));     } } 

So when I use the Package Manager Console on the local machine all works fine. But when I deploy to the environment I get the discrepancy to the file.

Can I run my static SQL scripts via EF migration automatically at all, or I should paste the SQL query inline in code?

Answer 1

I found the several answers for this question.

1. Add scripts as project resources and use it like:

   string sql = Resources._20170630085940_AddMigration; migrationBuilder.Sql(sql); 

This option not so good, because the .sql will embed in the assembly.

2. If you use Net Core projects with .csproj structure, you can add the itemgroup to xml:

   <ItemGroup> <Content Include="Migrations\**\*.sql" CopyToPublishDirectory="PreserveNewest" /><!-- CopyToPublishDirectory = { Always, PreserveNewest, Never } --></ItemGroup> 

And then specify the path to file like:

Path.Combine(AppContext.BaseDirectory, relativePath)