.Net application development specialists
asp.net, c#, vb.net, html, javascript, jquery, html, xhtml, css, oop, design patterns, sql server, mvc and much more
contact: admin@paxium.co.uk

Paxium is the company owned by myself, Dave Amour and used for providing IT contract development services including


  • Application development - Desktop, Web, Services - with Classic ASP, Asp.net WebForms, Asp.net MVC, Asp.net Core
  • Html, Css, JavaScript, jQuery, React, C#, SQL Server, Ado.net, Entity Framework, NHibernate, TDD, WebApi, GIT, IIS
  • Database schema design, implementation & ETL activities
  • Website design and hosting including email hosting
  • Training - typically one to one sessions
  • Reverse Engineering and documentation of undocumented systems
  • Code Reviews
  • Performance Tuning
  • Located in Cannock, Staffordshire
Rugeley Chess Club Buying Butler Cuckooland Katmaid Pet Sitting Services Roland Garros 60 60 Golf cement Technical Conformity Goofy MaggieBears Vacc Track Find Your Smart Phone eBate Taylors Poultry Services Lafarge Rebates System Codemasters Grid Game eBate DOFF

How to decode the Model column of the MigrationHistory table in EF Migrations

Recently I had to synchronize the entities of a C# application with its corresponding database objects. The application used EF code first with database migrations. There were multiple developers working with the project simultaneously so we ended up with some migrations in the __MigrationHistory table that weren’t making sense. Nobody was recognizing some of them so I wanted to view what was inside their (obscure) Model column to understand what the changes were about.

The Model column in the __MigrationHistory table is a serialized version of your EDMX. If you thought you didn’t have an EDMX because you were using EF code first, now you know you *do* have one, it’s just hidden 

The data type of the Model column is varbinary(max) so the first step in order to view its contents is to get a base64 representation of the binary stream.

MigrationHistory table Model column

Select from MigrationHistory

One way to get the Model column as base64 is by using T-SQL:

declare @binaryContent varbinary(max);
 
select @binaryContent = Model
    from [dbo].[__MigrationHistory]
where migrationId = '201304071557122_InitialCreate'
 
select cast('' as xml).value('xs:base64Binary(sql:variable("@binaryContent"))',
    'varchar(max)') as base64Content

Model column base64

The base64 that you get is your EDMX gzipped so our next step is to decompress it. Here’s a way to do using a console application:

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string modelBase64 = @"H4sIAAAAAAAEAN1ZS2/bOBC+L7D/QdBxgVqx20M3sFukeSyCrZMgSnstaGnsEEuRWpIy7N+2h/1J+xd2qJcp0rKdJ4JegojkfPMeztD//fPv+PMqY8ESpKKCT8Lh4CgMgCcipXwxCQs9f/cx/Pzp11/G52m2Cr43596bc0jJ1SS81zo/jiKV3ENG1CCjiRRKzPUgEVlEUhGNjo5+j4bDCBAiRKwgGN8WXNMMyg/8PBU8gVwXhE1FCkzV67gTl6jBFclA5SSBSThdJid5zmhCNIoyHFQUg7MZgmhY6TA4YZSgXDGweRjkH46/KYi1FHwR50hD2N06B9yfE6ag1uI4/3CoIkcjo0hEOBe6FOFRhghbFVHJczSGXhuxSkUnIUos7RN45k9YdxZw6UaKHKRe38K8prtMwyDq0kUuYUtm0RjW+B/X70dhcFUwRmYMWguhCWMtJPwBHCTRkN4QrUFyQwul6B5Xh4f523BBV2BshcEFXUH6FfhC37ecpmTVrAyPMMK+cYqhiERaFrBFst1czzNC2bOyrb4drldkSRdlJDj8T9JUglKgwuAWWHlC3dO8isyB8fEP68iFFNmtYLX3Nzs/YlHIxJhPbN2+I3IBuivVONqE1M5Aq2F+plgz5tnPZzdGbZavlMPw9ePW4j56+fB1mN8IpQ31gxk/W9qUxe9JGdOkRE/GNAl1SMacKCUSWkpiiWdJ0VXqnKfB7iSujNqogzYsmKbmOkPmaEbPTL2IrZIbxCabHdDfPFDMYpAmnQjDW1NpSSjXfspTntCcsJ0aOVQHlgpj8Bbf3TmDHLjJ9p2KH8K4qQU+85aHU8H2WWYcWRHhl1bTgyAFyFqA6XrTl7h+Nedj0Jao6LlNFHaixAuKLrGVFB5AGxQOhqVHVwo7xaxD25PQNezeBGhFb1X2nLM35C0MS1b3MuoquCXXW2dt2s2o6jebvjTqaUzHU5LnWBatRrVeCeKqSz19Fz+8L8wqjChRW9rDVtqWE96TZAHOLrJGSS+oVPqMaDIjplCfppl37IDQbDjZEeo7q4m35rT5v47+Pa36wO9zbVteoHqZKQNlR+AEjk9WTguEEbml6TgVrMh4fzXqp65aV5u+WjkcoW5DbYh6yccYR47uXuHy7O1Eveu8g1y7K5+f4N6t3eVBHu6lfBknN/eEjdB3d/SjdLtGG6u78yjEUS/i6CGIm/bORtusvnJIeqXYPdJyb0uyU3rHdRnc/3Dg1cXqSBigiZY0NTUxXisN2cAcGMR/s1NGUd/NgSnhdA5K34m/AO9DLNsfndeGR7wEREql7C0+B1Cj+t5h7IGzjf0SwJdEJvdE+hPMUwb9Q1D9geTtT8wv4o7usFyyeIZR+dkcu20Sfpx/9w+6PbiPC5snjY1PmhLbdv9V58LXGAP7u4k3OPj5Tfj+8a93+qsuqkmYzgT6tYrFrcNT32C4ayzcBt4/Vb3G1OgNiTtnREfFTvv4IjOh33Bg9Fi/Z2DgKrrYQJhfNzgknbhpz1zyuWjC15GoOeLUrSlokmJQnUhN5yTRuJ2gruUj3XfCivI+nEF6ya8LnRcaVYZsxjrvpiYNdvEvB9+uzOPrvHyOew4VUEyKKsA1/1JQlrZyX/h3Qh+Eya/6HjS+1OY+XKxbpCvBDwSqzdeWhTvIcoZg6prHZAn9su23Yddi4zNKFpJkqsbY0OMnhl+arT79D98O2NmRGwAA";           
 
            byte[] bytes = Convert.FromBase64String(modelBase64);
            byte[] uncompressed = Decompress(bytes);
 
            string edmx = Encoding.UTF8.GetString(uncompressed);
            File.WriteAllText(@"c:\temp\edmx.xml", edmx);
        }
 
        static byte[] Decompress(byte[] gzip)
        {
            using (GZipStream stream = new GZipStream(new MemoryStream(gzip), CompressionMode.Decompress))
            {
                const int size = 4096;
                byte[] buffer = new byte[size];
 
                using (MemoryStream memory = new MemoryStream())
                {
                    int count = 0;
                    do
                    {
                        count = stream.Read(buffer, 0, size);
                        if (count > 0)
                        {
                            memory.Write(buffer, 0, count);
                        }
                    }
 
                    while (count > 0);
                    return memory.ToArray();
                }
            }
        }
    }
}

The resulting EDMX xml looks like this:

EDMX xml