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)

Answer 2

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

Answer 3

You can create a helper method in same project first:

public static class SqlFileTrigger
{
    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..

Answer 4

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:

.

Answer 5

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

    }
}

Answer 6

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

Answer 7

Following the Accepted answer from @shkapo

I Add the code below to my .csproj file

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

And made my own extension Method:

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.EntityFrameworkCore.Migrations.Operations.Builders;
using System.Diagnostics;

namespace MigrationExtensions;

public static class MigrationExtensions
{
    public enum MigrationDirection
    {
        Up,
        Down
    }

    /// <summary>
    /// Execute a .sql file on the a EF Migration
    /// </summary>
    /// <param name="direction">Optional parameter, it add a .Up or a .Down at the end of the file name Ex.: "20221227004545_Initial.Up.sql"</param>
    /// <param name="fileName">Optional parameter, if not informed get the name of the caller class as name of the Sql file.</param>
    /// <param name="filesPath">Change the relative path where the sql files will be looked for</param>
    /// <param name="onWrongFilesPathThrowException">true: throw a exception if the file is not found, false: try to find sql file by the file name</param>
    /// <returns></returns>
    /// <exception cref="FileNotFoundException"></exception>
    public static OperationBuilder<SqlOperation> ExecuteSqlFile(
        this MigrationBuilder migrationBuilder,
             MigrationDirection? direction = null,
             string? fileName = null,
             string filesPath = "Migrations/SqlFiles",
             bool onWrongFilesPathThrowException = true)
    {
        if (fileName == null)
        {
            //Get stack to get the name of the calling Migration
            var frame = new StackFrame(1);
            string className = frame.GetMethod()!.DeclaringType!.Name;
            fileName = $"{className}{(direction != null ? $".{direction}" : "")}.sql";
        }
        else if (!Path.HasExtension(fileName))
        {
            fileName = $"{fileName}.sql";
        }

        string fileFullPath = Path.Combine(AppContext.BaseDirectory, filesPath, fileName);
        if (!File.Exists(fileFullPath))
        {
            string? alternativePath = Directory.EnumerateFiles(path: AppContext.BaseDirectory, fileName, SearchOption.AllDirectories).FirstOrDefault();
            if (alternativePath != null)
            {
                fileFullPath = onWrongFilesPathThrowException
                    ? throw new FileNotFoundException($"\"{Path.Combine(filesPath, fileName)}\" does not exists. There a file with the same name in {alternativePath.Replace(AppContext.BaseDirectory, "")}", fileFullPath)
                    : alternativePath;
            }
            else
                throw new FileNotFoundException($"\"{fileName}\" was not found in any directory.", fileFullPath);
        }

        return migrationBuilder.Sql(fileFullPath);
    }
}

Can be called with just migrationBuilder.ExecuteSqlFile();

Answer 8

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.