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

This is an upgrade of method that uses EmbeddedResource. The main idea is to use abstract class and a sql file with same name as migration.

public abstract class SqlMigration : Migration
{
    protected sealed override void Up(MigrationBuilder migrationBuilder)
    {
        var assembly = Assembly.GetExecutingAssembly();
        var type = GetType();
        var regex = new Regex($@"{Regex.Escape(type.Namespace)}\.\d{{14}}_{Regex.Escape(type.Name)}\.sql");

        var resourceName = assembly.GetManifestResourceNames().FirstOrDefault(x => regex.IsMatch(x));
        using var stream = assembly.GetManifestResourceStream(resourceName);
        using var reader = new StreamReader(stream);
        var sqlResult = reader.ReadToEnd();
        migrationBuilder.Sql(sqlResult);
    }
}

It just use name and namespace of real type for regular expression. The inherit class will looks like:

public partial class RunSqlScript : SqlMigration
{
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Down code here
    }
}

Project will be looks similar to:

.

I created an extensions method based on 4lexKislitsyn's answer. This assumes that your files end with a .sql extension and that it's an embeded resource in whatever project your running the migrations from. You could of course move the .sql part to the Up of your migration but this seemed cleaner to me.

public static class MigrationExtensions
{
    public static void RunSqlScript(this MigrationBuilder migrationBuilder, string script)
    {
        var assembly = Assembly.GetExecutingAssembly();
        var resourceName = assembly.GetManifestResourceNames().FirstOrDefault(x => x.EndsWith($"{script}.sql"));
        using var stream = assembly.GetManifestResourceStream(resourceName);
        using var reader = new StreamReader(stream);
        var sqlResult = reader.ReadToEnd();
        migrationBuilder.Sql(sqlResult);
    }
}

Used as

public partial class AddViews : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RunSqlScript("nameOfMyFile");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

I've found that evaluating the sql file name from the provided MigrationAttribute the best approach.

  public class EmbeddedSqlFileMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            var assembly = Assembly.GetExecutingAssembly();
            var type = GetType();
            var migrationAttribute = type.GetCustomAttribute<MigrationAttribute>();
            if (migrationAttribute == null)
                throw new InvalidOperationException("A migration requires a MigrationAttribute.");

            var sqlResourceFilename = $"{type.Namespace}.{migrationAttribute.Id}.sql";
            var resourceName = assembly.GetManifestResourceNames().FirstOrDefault(r => r == sqlResourceFilename);
            if (resourceName == null)
            {
                throw new FileNotFoundException(
                    $"Embedded resource '{sqlResourceFilename}' was not found in assembly '{assembly.FullName}'.");
            }

            using var stream = assembly.GetManifestResourceStream(resourceName);
            if (stream == null)
            {
                throw new InvalidOperationException(
                    $"Unable to get stream for embedded resource '{sqlResourceFilename}' in assembly '{assembly.FullName}'.");
            }

            using var reader = new StreamReader(stream);
            var sqlResult = reader.ReadToEnd();
            migrationBuilder.Sql(sqlResult);
        }
    }

You can create a helper method in same project first:

public static string GetRawSql(string sqlFileName)
{
    var baseDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Migrations");
    var path = Path.Combine(baseDirectory, sqlFileName);
    return File.ReadAllText(path);
}

And then add your migration. Let's say you added as: add-migration AddDefaultUser and then it generated as below:

now add 2 sql files (incuding the SQL statement you want to run like an insert record etc..) with same name with postfix of _Up and _Down. So it will be like:

Then in your migration file UP and DOWN methods, call them by using MigrationBuilder object. So your migration file will look like below:

public partial class AddDefaultUser : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    { 
        migrationBuilder.Sql(SqlFileTrigger.GetRawSql("20220918043843_AddDefaultUser_Up.sql")); 
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {  
        migrationBuilder.Sql(SqlFileTrigger.GetRawSql("20220918043843_AddDefaultUser_Down.sql"));
    }
}

PS: MAKE SURE YOU SET YOUR SQL FILE PROPERTIES AS COPY ALWAYS TO ENSURE THEY ARE DEPLOYED.

Hope it helps..

my tech lead and I were trying to figure out why I hadn't found a simple answer to inserting statements yet. Neither was frustrated at the other, both were frustrated at the lack of simplicity we ran into.

We found:

1. https://www.learnentityframeworkcore.com/raw-sql
2. https://mycodingtips.com/2021/9/20/how-to-run-sql-scripts-in-a-file-using-ef-core-migrations 3.https://www.codeproject.com/Articles/1173837/BulkInsert-with-the-Entity-Framework
3. https://www.yogihosting.com/insert-records-entity-framework-core/

All are valid or a good start, NONE are as simple as this:

1. Generate the insert statements needed
2. Use a GO separator between each
3. Parse the file in a Console Project, count up to 500 Go Separators to make blocks of 500 statements
4. Run each of the blocks making a string of each block and use DbSet.FromRawSQL(block of inserts)

I'll write the approach and post the code once I'm done.