How to describe details of an automatic migration

Question

In Entity Framework Code-First you can enable automatic migrations based on code changes in the model.

By calling Update-Database, these changes are:

1. Registered in a [timestamp]_AutomaticMigration
2. Generate SQL scripts and migrate database
3. Store the migration in the table __MigrationHistory

In the __MigrationHistory table, the migration is described in the Model column as a large hex value. eg:

0x1F8B0800000000000400CD57CD6EDB3810BE2FB0EF20F0B40512313F976D20B5C8CAC9C2D83A09AAB4775A1ADBC492944A5281FD6C3DF491FA0A1D...

In the Package Manager Console within Visual Studio, you can retrieve a list of automatic migrations with Get-Migrations.

However, there doesn't seem to be a way to retrieve the details of a particular automatic migration -- whether that's SQL script, or which model & fields are being affected.

Is there a way that I'm unaware of to retrieve details of a particular automatic migration?

Answer 1

Yes, there is.

This value in table __MigrationHistory, is a GZiped Xml, that contains the EDMX schema.

I made this following code to retrieve the EDMX model (this code uses C# 6)

You can access this code on my gist also, with this link: https://gist.github.com/AlbertoMonteiro/45198dc80641ce1896e6

In this gist there is a C# 5 version, that you can paste in a console application.

#r "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.Data.dll"
#r "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.Xml.Linq.dll"
using System.Xml.Linq;
using System.IO.Compression;

public void DecompressDatabaseMigration(string migrationName, string databaseName)
{
    var connectionString = $@"Data source=.\sqlexpress;Initial catalog={databaseName};Integrated security=true";
    var sqlToExecute = String.Format("select model from __MigrationHistory where migrationId like '%{0}'", migrationName);

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        var command = new SqlCommand(sqlToExecute, connection);

        var reader = command.ExecuteReader();
        if (!reader.HasRows)
        {
            throw new Exception("Now Rows to display. Probably migration name is incorrect");
        }

        while (reader.Read())
        {
            var model = (byte[])reader["model"];
            var decompressed = Decompress(model);
            File.WriteAllText(@"C:\temp\edmx.xml", decompressed.ToString());
        }
    }
}

public XDocument Decompress(byte[] bytes)
{
    using (var memoryStream = new MemoryStream(bytes))
    {
        using (var gzipStream = new GZipStream(memoryStream, CompressionMode.Decompress))
        {
            return XDocument.Load(gzipStream);
        }
    }
}

DecompressDatabaseMigration(Args[1], Args[0]);  

As you can see, there is some syntax that is allowed in the new C# REPL that arrived with Visual Studio Update 1.

So I save this file with .csx extention and then I execute, in my case DecompileMigration.csx

csi DecompileMigration.csx

Then the script will put in my C:\temp folder the file emdx.xml and then I see the model in this particular migration.

To use the REPL of VS 2015 update 1, you can

Load the customized CMD called Developer Command Prompt for VS2015

Press Windows and then search for this name Developer Command Prompt for VS2015

Option 1

Open this in C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Visual Studio 2015\Visual Studio Tools

Option 2

Open a cmd session, and then execute this following command

call "%vs140comntools%vsdevcmd.bat"

Option 3

Open it in Visual Studio 2015 U1 in the menu VIEW > Other Windows > C# Interactive

If you use this option, the Args member in the script will not work, so you must replace the Args[0] for your database name, and Args[0] for your database name.

Answer 2

This question came from a misunderstanding between automatic migrations vs. explicit migrations.

I considered an explicit migration would require you to write all up/down changes.

Add-Migration with migration changes that would otherwise be picked up by an automatic migration are placed automatically into the explicit migration file.

Calling Update-Database after the explicit migration will migrate the changes, but now with a code file detailing the changes.