How to run migration SQL script using Entity Framework Core

asp.net-core c# ef-migrations 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?

1
11
9/21/2018 1:03:20 PM

Accepted Answer

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.

  1. 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)
17
7/12/2017 11:22:27 AM

Popular Answer

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)

Migration folder in the Solution Explorer

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 embeded 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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow