Entity Framework CTP 4 - Code First Custom Database Initializer

Question

I would like to implement a custom database initialization strategy so that I can generate the database schema and apply it to an EXISTING EMPTY SQL database using a supplied User ID and Password.

Unfortunately the built-in strategies don’t provide what I’m looking for:

// The default strategy creates the DB only if it doesn't exist - but it does 
// exist so this does nothing
Database.SetInitializer(new CreateDatabaseOnlyIfNotExists<DataContext>());

// Drops and re-creates the database but then this breaks my security mapping and 
// only works if using a “Trusted" connection
Database.SetInitializer(new RecreateDatabaseIfModelChanges<DataContext>());

// Strategy for always recreating the DB every time the app is run. – no good for 
// what I want
Database.SetInitializer(new AlwaysRecreateDatabase<DataContext>());

I have worked out the following but this does not create the ModelHash so I’m unable to use "context.Database.ModelMatchesDatabase()" to validate that the database schema has been created and prevent multiple initializations:

public class Initializer : IDatabaseInitializer<DataContext>  
{ 
    Public void InitializeDatabase(DataContext context)  
    {       
         // this generates the SQL script from my POCO Classes
         var sql = context.ObjectContext.CreateDatabaseScript();

         // As expected - when run the second time it bombs out here with "there is already an
         // object named xxxxx in the database"
         context.ObjectContext.ExecuteStoreCommand(sql); 

         this.seed(context)
         context.SaveChanges();
    }
}  

Questions:

Does anyone know how I can get/create the model hash? (which is an EdmMetadata Entity)

-Or-

Is there a better way of doing this in general using the Code First CTP?

Answer 1

I ran into the same problem. I didn't really solve it, but I managed to get a little nasty workaround running, so i can deploy my solution to AppHarbor ;)

Its a IDatabaseInitializer implementation, that doesn't delete the db, but just nukes all the constraints and tables, and then uses the ObjectContext.CreateDatabaseScript() method to generate the sql, and then I execute it as a storecommand. A lot like the above implementation in the question.

But i also added functionality to create a hash from the model and save it in db, and when it runs again it checks if the current model-hash matches the one i db. Just like the real code-first implementation.

I couldn't make it work with the build in context.Database.CompatibleWithModel(true) - but this should work just as well, and seeing as its a temporary workaround it should be fine.

using System;
using System.Data.Entity;
using System.Data.Entity.Database;
using System.Data.Entity.Design;
using System.Data.Entity.Infrastructure;
using System.Data.Metadata.Edm;
using System.Data.Objects;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Xml;
using System.Linq;

namespace Devtalk
{
    public class DontDropDbJustCreateTablesIfModelChanged<T> : IDatabaseInitializer<T> where T : DbContext
    {
        private EdmMetadata _edmMetaData;

        public void InitializeDatabase(T context)
        {
            ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
            string modelHash = GetModelHash(objectContext);

            if (CompatibleWithModel(modelHash, context, objectContext)) return;

            DeleteExistingTables(objectContext);
            CreateTables(objectContext);

            SaveModelHashToDatabase(context, modelHash, objectContext);
        }

        private void SaveModelHashToDatabase(T context, string modelHash, ObjectContext objectContext)
        {
            if (_edmMetaData != null) objectContext.Detach(_edmMetaData);

            _edmMetaData = new EdmMetadata();
            context.Set<EdmMetadata>().Add(_edmMetaData);

            _edmMetaData.ModelHash = modelHash;
            context.SaveChanges();
        }

        private void CreateTables(ObjectContext objectContext)
        {
            string dataBaseCreateScript = objectContext.CreateDatabaseScript();
            objectContext.ExecuteStoreCommand(dataBaseCreateScript);
        }

        private void DeleteExistingTables(ObjectContext objectContext)
        {
            objectContext.ExecuteStoreCommand(Dropallconstraintsscript);
            objectContext.ExecuteStoreCommand(Deletealltablesscript);
        }

        private string GetModelHash(ObjectContext context)
        {
            var csdlXmlString = GetCsdlXmlString(context).ToString();
            return ComputeSha256Hash(csdlXmlString);
        }

        private bool CompatibleWithModel(string modelHash, DbContext context, ObjectContext objectContext)
        {
            var isEdmMetaDataInStore = objectContext.ExecuteStoreQuery<int>(LookupEdmMetaDataTable).FirstOrDefault();
            if (isEdmMetaDataInStore == 1)
            {            
                _edmMetaData = context.Set<EdmMetadata>().FirstOrDefault();
                if (_edmMetaData != null)
                {
                    return modelHash == _edmMetaData.ModelHash;
                }
            }
            return false;
        }

        private string GetCsdlXmlString(ObjectContext context)
        {
            if (context != null)
            {
                var entityContainerList = context.MetadataWorkspace.GetItems<EntityContainer>(DataSpace.SSpace);
                if (entityContainerList != null)
                {
                    EntityContainer entityContainer = entityContainerList.FirstOrDefault();
                    var generator = new EntityModelSchemaGenerator(entityContainer);
                    var stringBuilder = new StringBuilder();
                    var xmlWRiter = XmlWriter.Create(stringBuilder);
                    generator.GenerateMetadata();
                    generator.WriteModelSchema(xmlWRiter);
                    xmlWRiter.Flush();
                    return stringBuilder.ToString();
                }
            }
            return string.Empty;
        }

        private static string ComputeSha256Hash(string input)
        {
            byte[] buffer = new SHA256Managed().ComputeHash(Encoding.ASCII.GetBytes(input));
            var builder = new StringBuilder(buffer.Length * 2);
            foreach (byte num in buffer)
            {
                builder.Append(num.ToString("X2", CultureInfo.InvariantCulture));
            }
            return builder.ToString();
        }

        private const string Dropallconstraintsscript =
            @"select  
                'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name  
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so";

        private const string Deletealltablesscript =
            @"declare @cmd varchar(4000)
                declare cmds cursor for 
                Select
                    'drop table [' + Table_Name + ']'
                From
                    INFORMATION_SCHEMA.TABLES

                open cmds
                while 1=1
                begin
                    fetch cmds into @cmd
                    if @@fetch_status != 0 break
                    print @cmd
                    exec(@cmd)
                end
                close cmds
                deallocate cmds";

        private const string LookupEdmMetaDataTable =
            @"Select COUNT(*) 
              FROM INFORMATION_SCHEMA.TABLES T 
              Where T.TABLE_NAME = 'EdmMetaData'";
    }
}