Comparing Migrations In Code Versus The Database - EF Core

Overview

This article provides the code for a simple console application which will compare the EF Core Code First migrations in your database against those you have in your code project.

Code

class Program
{
    static void Main(string[] args)
    {
        /*--- Inits ---*/
        var dbMigrations = GetDatabaseMigrations();

        var lastDbMigration = dbMigrations.LastOrDefault();

        var codeMigrations = GetCodeMigrations();

        var lastCodeMigration = codeMigrations.LastOrDefault();

        var dbName = GetDatabaseName();

        /*--- Show Summary ---*/
        Console.WriteLine($"Database                = {dbName}");
        Console.WriteLine($"Last Database Migration = {lastDbMigration}");
        Console.WriteLine($"Last Code Migration     = {lastCodeMigration}");
        Console.WriteLine();

        var bgc = Console.BackgroundColor;
        var fgc = Console.ForegroundColor;

        Console.BackgroundColor = (lastCodeMigration == lastDbMigration) ?
            ConsoleColor.Green :
            ConsoleColor.Red;

        Console.ForegroundColor = ConsoleColor.Black;

        Console.WriteLine(lastCodeMigration == lastDbMigration ? "MATCHING" : "*** NOT matching ***");
        Console.WriteLine();
        Console.BackgroundColor = bgc;
        Console.ForegroundColor = fgc;

        /*--- Show Code Migrations Not In Database ---*/
        var missingMigrations = codeMigrations
            .Except(dbMigrations)
            .ToList();

        if (missingMigrations.Count() > 0)
        {
            Console.WriteLine("===== Code Migrations Not In Database =====");
            var i = 0;
            foreach (var m in missingMigrations)
            {
                i++;
                Console.WriteLine($"{i} - {m}");
            }
            Console.WriteLine();
        }

        /*--- Show Database Migrations Not In Code ---*/
        missingMigrations = dbMigrations
            .Except(codeMigrations)
            .ToList();

        if (missingMigrations.Count() > 0)
        {
            Console.WriteLine("===== Database Migrations Not In Code =====");
            var i = 0;
            foreach (var m in missingMigrations)
            {
                i++;
                Console.WriteLine($"{i} - {m}");
            }
            Console.WriteLine();
        }

        /*--- Clean Up ---*/
        Console.WriteLine("Press ENTER...");
        Console.ReadLine();
    }

    static List<string> GetCodeMigrations()
    {
        var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

        var items = path.Split(Path.DirectorySeparatorChar).AsQueryable();

        path = string.Join(Path.DirectorySeparatorChar, items.Take(items.Count() - 4));

        path = Path.Combine(path, "Acme.Project.DAL", "Migrations"); // TODO

        var found = Directory.Exists(path);

        var migrationFiles = Directory.GetFiles(path, "20*.cs")
            .Where(a => !a.Contains(".Designer."))
            .Select(a => Path.GetFileNameWithoutExtension(a))
            .AsQueryable()
            .OrderBy(x => x)
            .ToList();

        return migrationFiles;
    }

    static List<string> GetDatabaseMigrations()
    {
        var result = new List<string>();

        var config = GetConfiguration();

        var cs = config.GetConnectionString("AcmeDataModel"); // TODO

        using (var conn = new SqlConnection(cs))
        {
            conn.Open();

            var sql = "SELECT MigrationId FROM dbo.__EFMigrationsHistory ORDER BY MigrationId";

            using (var cmd = new SqlCommand(sql, conn))
            {
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var mid = reader.GetString(0);

                    result.Add(mid);
                }
            }
        }

        return result;
    }

    static string GetDatabaseName()
    {
        var config = GetConfiguration();

        var cs = config.GetConnectionString("AcmeDataModel"); // TODO

        var csb = new SqlConnectionStringBuilder(cs);

        return csb.InitialCatalog;

    }

    static IConfiguration GetConfiguration()
    {
        var currDir = Directory.GetCurrentDirectory();

        IConfigurationRoot config = new ConfigurationBuilder()
            .SetBasePath(currDir)
            .AddJsonFile("appsettings.json")
            .Build();

        return config;
    }

}