Automatic database migration with EF Core 3.0

AutoMigration.cs

using Microsoft.CodeAnalysis;
using Microsoft.CodeAnalysis.CSharp;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design.Internal;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Design;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Runtime;
using System.Runtime.Loader;
using System.Text;
using System.Threading.Tasks;

namespace ...
{
    public class AutoMigration : IOperationReporter
    {
        private readonly DbContext db;
        private readonly ILogger logger;

        public enum MigrationResult
        {
            Noop,
            Created,
            Migrated,
            AutoMigrated
        }

        public AutoMigration(DbContext db, ILogger logger)
        {
            this.db = db;
            this.logger = logger;
        }

        public bool AllowDestructive { get; set; } = true;
        public bool MigrateNewDatabase { get; set; } = false;

        void IOperationReporter.WriteError(string message) => logger.LogError(message);
        void IOperationReporter.WriteInformation(string message) => logger.LogInformation(message);
        void IOperationReporter.WriteVerbose(string message) => logger.LogTrace(message);
        void IOperationReporter.WriteWarning(string message) => logger.LogWarning(message);

        private DbCommand newCmd()
        {
            var conn = db.Database.GetDbConnection();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = db.Database.CurrentTransaction?.GetDbTransaction();
            return cmd;
        }

        // load the last model snapshot from the database
        private async Task<string> ReadSnapshotSource()
        {
            using var cmd = newCmd();
            cmd.CommandText = "select snapshot from auto_migration";

            await db.Database.OpenConnectionAsync();
            try
            {
                using var reader = cmd.ExecuteReader();

                if (!await reader.ReadAsync())
                    return null;

                using var stream = new GZipStream(reader.GetStream(0), CompressionMode.Decompress);
                return await new StreamReader(stream).ReadToEndAsync();
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                await db.Database.CloseConnectionAsync();
            }
        }

        private async Task WriteSnapshotSource(string source)
        {
            // write snapshot into the database
            await db.Database.ExecuteSqlRawAsync(
                @"IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name='auto_migration' and xtype='U')
                        CREATE TABLE auto_migration (snapshot varbinary(max) null)"
            );
            await db.Database.ExecuteSqlRawAsync(
                @"insert into auto_migration(snapshot) select null where not exists(select 1 from auto_migration)"
            );

            using var dbStream = new MemoryStream();
            using (var blobStream = new GZipStream(dbStream, CompressionLevel.Fastest, true))
            {
                await blobStream.WriteAsync(Encoding.UTF8.GetBytes(source));
            }
            dbStream.Seek(0, SeekOrigin.Begin);

            await db.Database.ExecuteSqlInterpolatedAsync($"update auto_migration set snapshot = {dbStream.ToArray()}");
        }

        private T Compile<T>(string source, IEnumerable<Assembly> references) 
        {
            var options = CSharpParseOptions.Default
                .WithLanguageVersion(LanguageVersion.Latest);

            var compileOptions = new CSharpCompilationOptions(OutputKind.DynamicallyLinkedLibrary)
                .WithAssemblyIdentityComparer(DesktopAssemblyIdentityComparer.Default);

            var compilation = CSharpCompilation.Create("Dynamic",
                new[] { SyntaxFactory.ParseSyntaxTree(source, options) },
                references.Select(a => MetadataReference.CreateFromFile(a.Location)),
                compileOptions
            );

            using var ms = new MemoryStream();
            var e = compilation.Emit(ms);
            if (!e.Success)
                throw new Exception("Compilation failed");
            ms.Seek(0, SeekOrigin.Begin);

            var context = new AssemblyLoadContext(null, true);
            var assembly = context.LoadFromStream(ms);

            var modelType = assembly.DefinedTypes.Where(t => typeof(T).IsAssignableFrom(t)).Single();

            return (T)Activator.CreateInstance(modelType);
        }

        private ModelSnapshot CompileSnapshot(Assembly migrationAssembly, string source) =>
            Compile<ModelSnapshot>(source, new HashSet<Assembly>() {
                AppDomain.CurrentDomain.GetAssemblies().Where(a => a.GetName().Name == "netstandard").Single(),
                typeof(object).Assembly,
                typeof(DbContext).Assembly,
                migrationAssembly,
                db.GetType().Assembly,
                typeof(DbContextAttribute).Assembly,
                typeof(ModelSnapshot).Assembly,
                typeof(SqlServerValueGenerationStrategy).Assembly,
                typeof(AssemblyTargetedPatchBandAttribute).Assembly
            });

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "Just because")]
        private async Task<string> AutoMigrate(Assembly migrationAssembly, IModel oldModel, IModel newModel)
        {
            var builder = new DesignTimeServicesBuilder(migrationAssembly, Assembly.GetEntryAssembly(), this, null);
            var services = builder.Build(db);
            var dependencies = services.GetRequiredService<MigrationsScaffolderDependencies>();
            var name = dependencies.MigrationsIdGenerator.GenerateId("Auto");

            // insert an extra step to track the history of auto migrations
            var insert = dependencies.HistoryRepository.GetInsertScript(
                    new HistoryRow(
                        name,
                        (string)newModel.FindAnnotation("ProductVersion")?.Value ?? "Unknown version"
                    ));

            if (oldModel == null)
            {
                await db.Database.EnsureCreatedAsync();
                await db.Database.ExecuteSqlRawAsync(dependencies.HistoryRepository.GetCreateScript());
                await db.Database.ExecuteSqlRawAsync(insert);
            }
            else
            {
                // apply fixes for upgrading between major / minor versions
                oldModel = dependencies.SnapshotModelProcessor.Process(oldModel);

                var operations = dependencies.MigrationsModelDiffer
                    .GetDifferences(oldModel, newModel)
                    // Ignore all seed updates. Workaround for (https://github.com/aspnet/EntityFrameworkCore/issues/18943)
                    .Where(o => !(o is UpdateDataOperation)) 
                    .ToList();

                if (!operations.Any())
                    return null;

                if (!AllowDestructive && operations.Any(o => o.IsDestructiveChange))
                    throw new InvalidOperationException(
                        "Automatic migration was not applied because it could result in data loss.");

                operations.Add(
                    new SqlOperation(){
                        Sql = insert
                    });

                // Convert the operations to sql, then execute the operations
                var sqlGenerator = db.GetService<IMigrationsSqlGenerator>();
                var commands = sqlGenerator.Generate(operations, db.Model);
                var executor = db.GetService<IMigrationCommandExecutor>();

                await executor.ExecuteNonQueryAsync(commands, db.GetService<IRelationalConnection>());
            }

            var codeGen = dependencies.MigrationsCodeGeneratorSelector.Select(null);
            return codeGen.GenerateSnapshot("AutoMigrations", db.GetType(), $"Migration_{name}", newModel);
        }

        // Migrate the database by first applying release migrations, then by auto migrating from the model snapshot stored in the database
        public async Task<MigrationResult> Migrate()
        {
            var ret = MigrationResult.Noop;

            var migrationAssembly = db.GetService<IMigrationsAssembly>();
            var migrations = db.Database.GetMigrations();
            var appliedMigrations = (await db.Database.GetAppliedMigrationsAsync()).ToList();
            var migrateDatabase = MigrateNewDatabase || migrations.Intersect(appliedMigrations).Any();

            var pendingMigrations = migrateDatabase && migrations.Except(appliedMigrations).Any();
            var devMigration = appliedMigrations.Except(migrations).LastOrDefault();

            ModelSnapshot modelSnapshot = null;
            if (devMigration != null)
            {
                if (pendingMigrations)
                    throw new InvalidOperationException("An automatic migration has been run, but you've added new release migration(s).\nYou'll need to restore from a release database.");

                var source = await ReadSnapshotSource();
                if (source == null || !source.Contains(devMigration))
                    throw new InvalidOperationException($"Expected to find the source code of the {devMigration} ModelSnapshot stored in the database");

                modelSnapshot = CompileSnapshot(migrationAssembly.Assembly, source);
            }
            else
            {
                if (migrateDatabase)
                {
                    if (pendingMigrations)
                    {
                        // Run release migrations
                        await db.Database.MigrateAsync();
                        ret = MigrationResult.Migrated;
                    }

                    modelSnapshot = migrationAssembly.ModelSnapshot;
                }
            }

            var newSnapshot = await AutoMigrate(migrationAssembly.Assembly, modelSnapshot?.Model, db.Model);
            if (newSnapshot != null)
            {
                ret = appliedMigrations.Any() ? MigrationResult.AutoMigrated : MigrationResult.Created;
                await WriteSnapshotSource(newSnapshot);
            }

            return ret;
        }
    }
}

This code is provided as-is, with no implied warranty. Read its public interface, study what it is doing, step through it with a debugger. Use at your own risk, test your own use-cases. Miss-used, it really could nuke your entire database without any warning. If it ends up nuking your database, you have to take responsibility for that.

Is it possible that you show an example of how to integrate it correctly, pls?

Very nice! Appreciate the share.

Very useful! Thanks for this!

I can't use this code ,because my project can not reference IOperationReporter, my project use ef core 3.1.7 .Could you make thie code in a project , thanks

Hand edit your project file, remove the extra xml that is hiding internal classes from your code.

Very useful library. Please update this class library into .net 6.0

Very useful library. Please update this class library into .net 6.0 and net 7.0 , when update to net7.0 this line is error
var migrationAssembly = db.GetService();