An EntityFrameworkCore Migration base class for Sql Script File-backed Migrations.

SqlScriptFileMigration.cs

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
// ReSharper disable InconsistentNaming

#region migrations

[Migration(nameof(Script1))]public class Script1 : MigrationConfiguredWithScriptDirectoryAndDbContext{}
[Migration(nameof(Script2))]public class Script2 : MigrationConfiguredWithScriptDirectoryAndDbContext{}

#endregion


#region script directory configuration

/// <summary>
/// A <see cref="SqlScriptFileMigration"/> base class which is configured to know two
/// things:
/// <list type="bullet" >
/// <item>Which directory contains the script files : <see cref="ScriptFileDirectoryRelativePath"/></item>
/// <item>Which DbContext you are migrating :
/// named in the <see cref="DbContextAttribute"/> attribute's constructor.</item>
/// </list>
/// </summary>
[DbContext(typeof(MyDbContext))]
public abstract class MigrationConfiguredWithScriptDirectoryAndDbContext() : SqlScriptFileMigration(ScriptFileDirectoryRelativePath)
{
    /// <summary>
    /// Configure this to your db script directory, relative to your repo or solution root.
    /// </summary>
    const string PathFromRepoRootToDbScripts = "DbScripts/UnitTestDb/";

    /// <summary>
    /// This string does not need to be the actual path to the project bin directory, it only has to 
    /// have the right number of directory <em>levels</em>. It is for Path.GetRelativePath()'s calculation
    /// of how many directory levels there will be at run time, or when `ef database update` is run, from 
    /// this dll containing the migrations, to the repo or solution root where 
    /// <see cref="PathFromRepoRootToDbScripts"/> can be found.
    /// </summary>
    const string PathFromRepoRootToMigrationsDllAtRunTime = "ProjectDirectory/bin/BuildConfig/netVersion/";

    /// <summary>
    /// Work out where we will find the DbScripts directory when running migrations
    /// </summary>
    static readonly string ScriptFileDirectoryRelativePath
        = Path.GetRelativePath(
            PathFromRepoRootToMigrationsDllAtRunTime,
            PathFromRepoRootToDbScripts);
}
#endregion


#region sql script file migration base class

/// <summary>
/// A base class for EF migrations for teams using SQL script files
/// instead of autogenerated C# migration classes.
/// </summary>
/// <param name="ScriptsDirectory">
/// A relative or absolute path the directory where the migration script will be found.
/// If relative, it is taken to be relative at run-time to <see cref="AppContext.BaseDirectory"/>
/// which is typically what you want.
/// </param>
public abstract class SqlScriptFileMigration(string ScriptsDirectory) : Migration
{
    /// <summary>
    /// The first part of the migration script filenames.
    /// Defaults to the migration class name and is used for both up and down migrations.
    /// </summary>
    protected virtual string FileNamePrefix => GetType().Name;

    /// <summary>
    /// The last part of the sql script filename containing an Up, or deploy, migration.
    /// Defaults to "_Deploy".
    /// </summary>
    protected virtual string UpSuffix => "_Deploy";
    /// <summary>
    /// The last part of the sql script filename containing a Down, or rollback, migration.
    /// Defaults to "_Rollback".
    /// </summary>
    protected virtual string DownSuffix => "_Rollback";

    /// <summary>
    /// The file extension you will use for your SQL script files. Defaults to ".sql".
    /// </summary>
    protected virtual string extSql => ".sql";

    /// <summary>
    /// The full path of the script file to use for the Up, or deploy, migration.
    /// <p>The default implementation looks in <see cref="ScriptsDirectory"/>
    /// for a filename that is the concatenation of
    /// <see cref="FileNamePrefix"/>,
    /// <see cref="DownSuffix"/>,
    /// and <see cref="extSql"/>.
    /// </p>
    /// </summary>
    protected virtual string UpFilePath
                        => Path.Join(ScriptsDirectory,$"{FileNamePrefix}{UpSuffix}{extSql}");

    /// <summary>
    /// The full path of the script file to use for the Down, or rollback, migration.
    /// <p>This default implementation looks in <see cref="ScriptsDirectory"/>
    /// for a filename that is the concatenation of
    /// <see cref="FileNamePrefix"/>,
    /// <see cref="DownSuffix"/>,
    /// and <see cref="extSql"/>.
    /// </p>
    /// </summary>
    protected virtual string DownFilePath
                        => Path.Join(ScriptsDirectory,$"{FileNamePrefix}{DownSuffix}{extSql}");


    /// <summary>
    /// <p>Add the SQL script <paramref name="sqlfile"/> to <paramref name="migrationBuilder"/></p>
    /// </summary>
    /// <remarks
    /// Will be called when the EF tooling, <c>dotnet ef database update</c>, applies
    /// up (aka deploy) migrations
    /// </remarks>
    protected override void Up(MigrationBuilder mb) => AddSqlFile(mb,UpFilePath);

    /// <summary>
    /// <p>Add the SQL script <paramref name="sqlfile"/> to <paramref name="migrationBuilder"/></p>
    /// </summary>
    /// <remarks
    /// Will be called when the EF tooling, <c>dotnet ef database update</c>, applies
    /// down (aka rollback) migrations
    /// </remarks>
    protected override void Down(MigrationBuilder mb) => AddSqlFile(mb,DownFilePath);

    /// <summary>
    /// <p>Add the SQL script <paramref name="sqlFilePath"/> to <paramref name="migrationBuilder"/></p>
    /// </summary>
    ///
    /// <remarks
    /// Will be called via <see cref="Up"/> and <see cref="Down"/>
    /// when the EF tooling, <c>dotnet ef database update</c>, applies up or down migrations.
    /// </remarks>
    ///
    /// <param name="migrationBuilder"></param>
    /// <param name="sqlFilePath">
    /// If this is absolute, it will be used as is. If it is relative,
    /// it will be used relative to <see cref="AppContext.BaseDirectory"/>
    /// </param>
    /// <exception cref="FileNotFoundException"></exception>
    static void AddSqlFile(MigrationBuilder migrationBuilder, string sqlFilePath)
    {
        var file = new FileInfo(Path.Combine(AppContext.BaseDirectory, sqlFilePath));
        if(!file.Exists)throw new FileNotFoundException(
            $"In {AppContext.BaseDirectory}. Can't find migration script.",
            file.FullName);

        using var fileStream = file.OpenRead();
        using var streamReader = new StreamReader(fileStream);
        var sql = streamReader.ReadToEnd();
        migrationBuilder.Sql(sql);
    }
}

#endregion

• Use this if you write your own sql deploy and rollback scripts
• Note that you do not need to run ef migration add if you do this.
• Just run ef database update