EF5 Migrations: Multiple Data Contexts in a Single Database

Consider this: an application is built up from multiple modules, each with a different set of entities and data contexts. All modules’ data entities should be saved in a single database and the contexts should support automatic migration.

Sounds like a reasonable requirement, right? We’ll, you can’t do that with EF. Not with EF5 nor with EF6 (as far as I can tell from the current alpha version). The problem is this: The data context stores it’s models and mappings in the history table. Upon model modification checking, the current types and properties in the the data context are compared to the last version of the conceptual model in the history table. All modifications to the database result from this comparison.

So what happens when we have two data contexts that use the same database? Here’s an example of two data contexts:

public class A   
{   
    public string AId { get; set; }
}   

public class B   
{   
    public string AId { get; set; }   
}  

public class C  
{  
    public string AId { get; set; }  
}  

public class ContextA : DbContext  
{  
    public DbSet<A> A { get; set; }  
    public DbSet<B> B { get; set; }  
}  

public class ContextB : DbContext  
{  
    public DbSet<B> B { get; set; }  
    public DbSet<C> C { get; set; }  
}

Also, consider following usage of these contexts:

internal class Program   
{   
    private static void Main(string[] args)   
    {   
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<ContextA>());   
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<ContextB>());  

        using (var ctxA = new ContextA())  
        {  
            var a = ctxA.A.FirstOrDefault();  
        }  

        using (var ctxB = new ContextB())  
        {  
            var b = ctxB.B.FirstOrDefault();  
        }  
    }  
}

What happens on the first instantiation of ContextA at line 8? EF will recognize that the database has to be created – and it will do so. Also, it will store a value in the history table stating that there are two tables named A and B.

When ContextB is instantiated at line 13, EF will read the history table and figure out that table A is no longer in use and and should be dropped and that there is a new table C to be created – and it will do so.

So whenever one of these two contexts will be created, EF will drop whatever tables are contained in the database that have no corresponding sets in the context.

This means that your data context must contain all entities your application will be using. At least as long as you want them to be stored in a single database. Considering the sample from above, both contexts use the class B. This makes it hard to store both contexts in different databases. Suppose you were adding an entity B into ContextA, you’d expect ContextB to be able to retrieve that instance. This can only happen if both contexts are saved in the same database.

MigrationSqlGenerator to the rescue

Before you read on, notice that I am proposing a work around here. This is not how EF is intended to work, but it helped me in a couple of situation. Please  make sure that you really really need this.

The explanations above implicitly already contained the solution for the problem. If the problem is that EF drops tables unknown to a current context, then the solution must be to disable table dropping. EF5 does not provide a way to interfere with the model comparison, but at least it allows generating custom SQL based on the results of the comparison.

Behold the MigrationSqlGenerator class. This class has one single method:

public abstract IEnumerable<MigrationStatement> Generate(
    IEnumerable<MigrationOperation> migrationOperations, 
    string providerManifestToken);

It takes a collection of MigrationOperations which describe whatever EF decided to to with the database and results a collection of MigrationStatements which contain the generated SQL. EF5 already contains SQL generators for SQL Server and SQL CE, but we’ll need our own implementations that do a couple of things differently:

Skip dropping tables

If EF decided that a table should be dropped we’ll need to skip that. Also, we’ll need to skip dropping any objects from tables that EF decided to delete. For example, if EF decided that the table A must be dropped, it will generate MigrationOperations for the table drop as well as for dropping columns, foreign keys and indexes as well. Our implementation of the SQL generator must detect that a column, foreign key or index drop is related to a table drop and skip all of them. On the other hand, if a column, foreign key od index is dropped without dropping the table, there is a valid reason for that (the model has actually changed) and we’ll need to generate SQL for that.

internal static class EnumerableExtensions   
{   
    public static bool ItemsEqual<T>(this IEnumerable<T> items, object other)   
    {   
        if (other == null) return false;   
        Debug.Assert(other is IEnumerable<T>, "other must be IEnumerable<T>");   
        var others = (IEnumerable<T>) other;   
        return items != null && items.Count() == others.Count() && items.All(others.Contains);  
    }  

    public static IList<T> WhereIn<T>(this IEnumerable<object> items, IEnumerable<object> others, Func<T, dynamic, bool> predicate)  
    {  
        return items.OfType<T>().Where(op => others.Any(op2 => predicate(op, (dynamic) op2))).ToList();  
    }  
}  

public class SqlGenerator : MigrationSqlGenerator  
{  
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)  
    {  
        var operations = FilterMigrationOperations(migrationOperations.ToList());  

        // TODO: implement rest...  
    }  

    private static IEnumerable<MigrationOperation> FilterMigrationOperations(List<MigrationOperation> operations)  
    {  
        var dropTable = operations.OfType<DropTableOperation>().ToList();  
        var dropColumns = operations.WhereIn<DropColumnOperation>(dropTable, (op, op2) => op2.Name == op.Table);  
        var dropForeignKey = operations.WhereIn<DropForeignKeyOperation>(dropTable, (op, op2) => op2.Name == op.DependentTable || op2.Name == op.PrincipalTable);  
        var dropIndex = operations.WhereIn<DropIndexOperation>(dropForeignKey, (op, op2) => op2.DependentTable == op.Table && op.Columns.ItemsEqual(op2.DependentColumns as object));  
        var dropPrimaryKey = operations.WhereIn<DropPrimaryKeyOperation>(dropTable, (op, op2) => op2.Name == op.Table);  

        var createTable = operations.WhereIn<CreateTableOperation>(dropTable, (op, op2) => op2.Name == op.Name);  
        var addColumn = operations.WhereIn<AddColumnOperation>(dropColumns, (op, op2) => op2.Name == op.Column.Name && op2.Table == op.Table);  
        var addForeignKey = operations.WhereIn<AddForeignKeyOperation>(dropForeignKey, (op, op2) => op2.Name == op.Name && op2.DependentTable == op.DependentTable && op2.PrincipalTable == op.PrincipalTable);  
        var createIndex = operations.WhereIn<CreateIndexOperation>(dropIndex, (op, op2) => op.Table == op2.Table && op.Columns.ItemsEqual(op2.Columns as object));  
        var addPrimaryKey = operations.WhereIn<AddPrimaryKeyOperation>(dropPrimaryKey, (op, op2) => op.Table == op2.Table && op.Columns.ItemsEqual(op2.Columns as object));  

        var exceptions = new IEnumerable<MigrationOperation>[]  
        {  
            dropTable,  
            dropColumns,  
            dropForeignKey,  
            dropIndex,  
            dropPrimaryKey,  

            createTable,  
            addColumn,  
            addForeignKey,  
            createIndex,  
            addPrimaryKey  
        };  

        var filteredOperations = operations.Except(exceptions.SelectMany(o => o));  

        return filteredOperations.ToList();  
    }  
}

The above implementation of MigrationSqlGenerator is still not complete, but what it does until now is to detect all table drops and related object drops, detect all re-creations of tables and all related object creations and removing those operations from the original set of MigrationOperations.

Check if objects exist before creation

In the original implementation, EF’s SQL generators generate create statements without checking whether the table, column, key or index already exists. This can lead to exceptions in our scenario since we cannot guarantee that a table does not exist when we generate the creation code.

Here are examples of how to generate the SQL statements. Of course, there are many more statements to generate – one for each MigrationOperation that describes a creation operation.

protected override void Generate(AddColumnOperation addColumnOperation)
{
    using (var writer = Writer())
    {
        var column = addColumnOperation.Column;
        var nameParts = NameParts(addColumnOperation.Table);
        var tableName = nameParts.Last();
        var tableSchema = nameParts.First();

        writer.WriteLine("IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{1}' AND COLUMN_NAME = '{0}' AND TABLE_SCHEMA = '{2}')\r\nBEGIN", column.Name, tableName, tableSchema);
        writer.Indent++;
        writer.Write("ALTER TABLE ");
        writer.Write(Name(addColumnOperation.Table));
        writer.Write(" ADD ");

        Generate(column, writer);

        if (((column.IsNullable.HasValue && !column.IsNullable.Value) && ((column.DefaultValue == null) && string.IsNullOrWhiteSpace(column.DefaultValueSql))) && ((!column.IsIdentity && !column.IsTimestamp) && (!column.StoreType.EqualsIgnoreCase("rowversion") && !column.StoreType.EqualsIgnoreCase("timestamp"))))
        {
            writer.Write(" DEFAULT ");
            if (column.Type == PrimitiveTypeKind.DateTime)
            {
                writer.Write(Generate(DateTime.Parse("1900-01-01 00:00:00")));
            }
            else
            {
                writer.Write(Generate((dynamic) column.ClrDefaultValue));
            }
        }
        writer.Indent--;
        writer.Write("\r\nEND");

        Statement(writer);
    }
}

protected override void Generate(AddForeignKeyOperation addForeignKeyOperation)
{
    using (var writer = Writer())
    {
        writer.WriteLine("IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = '{0}')\r\nBEGIN", addForeignKeyOperation.Name);
        writer.Indent++;

        writer.Write("ALTER TABLE ");
        writer.Write(Name(addForeignKeyOperation.DependentTable));
        writer.Write(" ADD CONSTRAINT ");
        writer.Write(Quote(addForeignKeyOperation.Name));
        writer.Write(" FOREIGN KEY (");
        writer.Write(string.Join(", ", addForeignKeyOperation.DependentColumns.Select(Quote)));
        writer.Write(") REFERENCES ");
        writer.Write(Name(addForeignKeyOperation.PrincipalTable));
        writer.Write(" (");
        writer.Write(string.Join(", ", addForeignKeyOperation.PrincipalColumns.Select(Quote)));
        writer.Write(")");
        if (addForeignKeyOperation.CascadeDelete)
        {
            writer.Write(" ON DELETE CASCADE");
        }

        writer.Indent--;
        writer.Write("\r\nEND");

        Statement(writer);
    }
}

No there is an obstacle: for table creation operations, EF does not create a MigrationOperation for each added column, but instead adds the columns to be generated into the CreateTableOperation. That’s why we’ll need to replace each CreateTableOperation with a CreateTableOperation that does not contain any column additions and a set of AddColumnOperations for each contained column:

private static IEnumerable<MigrationOperation> FilterMigrationOperations(List<MigrationOperation> operations)
{
    // ... code from above...

    var filteredOperations = operations.Except(exceptions.SelectMany(o => o));

    return ReplaceAddTableOperation(filteredOperations).ToList();
}

private static IEnumerable<MigrationOperation> ReplaceAddTableOperation(IEnumerable<MigrationOperation> operations)
{
    foreach (var operation in operations)
    {
        var tableOperation = operation as CreateTableOperation;
        if (tableOperation != null)
        {
            var nonKeyColumns = tableOperation.Columns.Where(c => !tableOperation.PrimaryKey.Columns.Contains(c.Name)).ToList();

            foreach (var column in nonKeyColumns)
            {
                tableOperation.Columns.Remove(column);
            }

            yield return tableOperation;

            foreach (var column in nonKeyColumns)
            {
                yield return new AddColumnOperation(tableOperation.Name, column);
            }
        }
        else
        {
            yield return operation;
        }
    }
}

Setting the SQL generator

To use our custom SQL generator, we must assign it inside a custom implementation of DbMigrationsConfiguration:

internal sealed class MigrationConfiguration<T> : DbMigrationsConfiguration<T> where T : DbContext
{
    public MigrationConfiguration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;

        SetSqlGenerator("System.Data.SqlClient", new SqlServerGenerator());
    }
}

Then we’ll use the configuration like this:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<T, MigrationConfiguration<T>>());

And here’s the catch

Using the described approach undermines EF’s meta data mapping. Strictly speaking, this apporach corrupts your EF meta data.

Whenever a new data context is used, EF will detect differences between the model and the history table and modify the database. Depending on the total number of tables in the database, the generated SQL might get very large. This is not very performant. On the other side, no database modification attempts will occur if the code line above (Database.SetInitializer…) is omitted. This means that an application that uses the proposed solution will usually not set a database initializer. It will only set the initializer as mentioned above inside some kind of configuration mode where the lack of performance is acceptable.

---

You can download the complete implementaion here. Please note that the code may corrupt your database. Use it at you own risk. You can use the code in any way you want and you don’t need to bother with any licensing issues. But just out of interest I would like to know if it was of help for anyone – please leave me a note when you do use the code.