Programmatically Validate EF6/MySQL MigrationHistory in DB vs Assembly

Introduction

I'm developing an EF6 code-first ASP.NET app using MySql. We're in the early stages, and despite our best efforts at DB design, we update the migrations often enough that the DB gets out of sync with the random developer.

Unfortunately, EF6 doesn't check migrations internally. The mismatch manifests itself as an arcane exception when the Context is first exercised (query, etc.). So, to end confusion and provide a more useful exception, decided to have the code self-check that it is in sync with the DB.

I call ValidateMigration() from my ASP.NET app's Application_Start() in order to trap the error as early as possible.

Using the Code

Though we're EF6 and ASP.NET based, the code in this technique isn't tied to either of those; it uses plain non-EF MySQL and has no ASP.NET associations.

using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using MySql.Data.MySqlClient;

// This checks the DB vs the assembly to ensure both have the same migration.
// Throws exception on any failure.
static public void ValidateMigration(string connectionString)
{
	string dbLatestMigration = "";
	try
	{
		using (var connection = new MySqlConnection(connectionString))
		{
			connection.Open();

			string q = "SELECT MigrationId FROM __MigrationHistory;";
			using (MySqlCommand command = new MySqlCommand(q, connection))
			{
				using (MySqlDataReader reader = command.ExecuteReader())
				{
					if (reader.HasRows)
					{
						while (reader.Read())
						{
							dbLatestMigration = (string)reader["MigrationId"];
						}
					}
				}
			}
		}
	}
	catch (Exception ex)
	{
		throw new Exception("ValidateMigration", ex);
	}

	if(dbLatestMigration.IsNullOrEmpty())
	{
		throw new Exception("Couldn't find latest migration in DB");
	}


    var assyMigs = (
        from t in Assembly.GetExecutingAssembly().GetTypes()
        where t.BaseType == (typeof(DbMigration)) && t.GetConstructor(Type.EmptyTypes) != null
        select (DbMigration)Activator.CreateInstance(t)
    );

    DbMigration assyMigration = (from m in assyMigs
        where ((IMigrationMetadata)m).Id == dbLatestMigration
        select m).FirstOrDefault();

	if(assyMigration == null)
	{
		throw new Exception("Couldn't find latest migration in Assembly");
	}

	string assyLatestMigration = ((IMigrationMetadata)lastMigration).Id;
	if (assyLatestMigration != dbLatestMigration)
	{
		throw new Exception("Migration mismatch; 
		db=[" + dbLatestMigration + "] Assembly=[" + assyLatestMigration + "]");
	}

	// All good!!!
}

Attribution

I lifted snippets from http://tech.trailmax.info/2014/03/inside_of_ef_migrations/.