How to run migration SQL script using Entity Framework Core

What I like to do is to embed the SQL script as a resource in the assembly so that the assembly does not depend on any external file. I have tested this approach with Visual Studio Community 2019 16.4.2. In my case the DbContext is hold in .NET Standard 2.0 library and my web application is running .NET Core 2.2.

First you need to create a migration file:

1. In Visual Studio make sure to set as start up project the web application.
2. In Visual Studio open the PMC: View -> Other Windows -> Package Manager Console (PMC)
3. In PMC Set the default project to the project that holds the DbContext (the .NET standard 2.2 library in my case)

4. Add a new migration:

   Add-Migration RunSqlScript

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

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

var assembly = Assembly.GetExecutingAssembly();
string resourceName = typeof(RunSqlScript).Namespace + ".20191220105024_RunSqlScript.sql";
using (Stream stream = assembly.GetManifestResourceStream(resourceName))
{
  using (StreamReader reader = new StreamReader(stream))
  {
    string sqlResult = reader.ReadToEnd();
    migrationBuilder.Sql(sqlResult);
  }
}

In my app I have re-factorize this code into a utility method. I have posted without this re-factorization for the sake of brevity.

UPDATE:

The re-factorized code I mentioned above:

public static class MigrationUtility
{
  /// <summary>
  /// Read a SQL script that is embedded into a resource.
  /// </summary>
  /// <param name="migrationType">The migration type the SQL file script is attached to.</param>
  /// <param name="sqlFileName">The embedded SQL file name.</param>
  /// <returns>The content of the SQL file.</returns>
  public static string ReadSql(Type migrationType, string sqlFileName)
  {
    var assembly = migrationType.Assembly;
    string resourceName = $"{migrationType.Namespace}.{sqlFileName}";
    using (Stream stream = assembly.GetManifestResourceStream(resourceName))
    {
      if (stream == null)
      {
        throw new FileNotFoundException("Unable to find the SQL file from an embedded resource", resourceName);
      }

      using (var reader = new StreamReader(stream))
      {
        string content = reader.ReadToEnd();
        return content;
      }
    }
  }
}

Usage example:

string sql = MigrationUtility.ReadSql(typeof(RunSqlScript), "20191220105024_RunSqlScript.sql");
migrationBuilder.Sql(sql);

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)