April 2015

Volume 30 Number 4

---

Data Points - EF6 Code First Migrations for Multiple Models

ByJulie Lerman

Entity Framework 6 introduced support for Code First Migrations to better handle storing data for multiple models in a single data­base. But the support is very specific and may not be what you imagine. In this article, you’ll learn about this feature, what it does and doesn’t do, and see how to use it.

Distinct Models, Distinct Tables, Distinct Data: Same Database

EF6 migrations supports migration of multiple models that are completely independent of one another. Both implementations of this feature—using a key to identify a set of migrations or using database schema to group migration histories with the tables for a single model—allow you to store separate, distinct models in the same database.

Not for Sharing Data Across Models or for Multi-Tenant Databases

It’s easy to misinterpret the benefits of this feature, so I want to be clear right away on what is not supported.

This new multi-model support isn’t designed to replicate a single model and across multiple schemas to have a multi-tenant database.

The other pattern many of us hope for when we see this feature is the ability to share a common entity (and its data) across multiple models and map the entity to a single database. That’s a very different kind of problem, however, and not one that’s easily solved with Entity Framework. I used to try but gave up. I’ve written previous articles on sharing data across databases in this column (see “A Pattern for Sharing Data Across Domain-Driven Design Bounded Contexts, Part 2” at bit.ly/1817XNT). I also presented a session at TechEd Europe called “Entity Framework Model Partitioning in Domain-Driven Design Bounded Contexts,” which was recorded and is available at bit.ly/1AI6xPa.

Pattern One: ContextKey Is the Key

One of the new tools EF6 provides to enable this feature is the ContextKey. This is a new field in the MigrationHistory table of the database that keeps track of every migration. It’s partnered with a new property of the same name in the DbMigrations­Configuration<TContext> class.

By default, the ContextKey will inherit the strongly typed name of the DbMigrationsConfiguration associated with that context. As an example, here’s a DbContext class that works with Doctor and Episode types:

namespace ModelOne.Context
{
  public class ModelOneContext:DbContext
  {
    public DbSet<Doctor> Doctors { get; set; }
    public DbSet<Episode> Episodes { get; set; }
  }
}

As always, the default behavior of the enable-migrations command is to create a DbMigrationsConfiguration class that has the name [YourNamespace].Migrations.Configuration.

When I apply a particular migration (that is, when I call Update-­Database in the Visual Studio Package Manager Console), Entity Framework will not only apply the migration, it will also add a new row to the __MigrationHistory table. Here’s the SQL for that action:

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model],
  [ProductVersion])
VALUES (N'201501131737236_InitialModelOne',
  N'ModelOne.Context.Migrations.Configuration',
  [hash of the model], N'6.1.2-31219')

Notice that the value going into the ContextKey field is Model­One.Context.Migrations.Configuration, which is the strongly typed name of my DbMigrationsConfiguration<TContext> class.

You can control the ContextKey name by specifying the Context­Key property of the DbMigrationsConfiguration class in the class constructor. I’ll rename it to ModelOne:

public Configuration()
{
  AutomaticMigrationsEnabled = false;
  ContextKey = "ModelOne";
}

Now executing migrations will use ModelOne for the ContextKey field of the Migration table. However, if you’ve already executed migrations with the default, this will not go well. EF will attempt to reapply all of the migrations, including those that created tables and other database objects, causing the database to throw errors because of the duplicate objects. So my advice is to change that value prior to applying any migrations, otherwise you’ll have to manually update the data in the __MigrationsHistory table. 

I’ve made sure my DbContext type points to a connection string that I’ve named MultipleModelDb. Rather than rely on the Code First convention to locate a connection string with the same name as the context, I want to have a single connection string I can use for any model that targets this database. I did this by specifying that the context constructor inherit the DbContext overload, which takes a connection string name. Here’s the constructor for ModelOneContext:

public ModelOneContext()
       : base("MultipleModelDb") {
}

Both add-migration and update-database will be able to find the connection string, so I’m assured of migrating the correct database.

Two Contexts, Two ContextKeys

Now that you see how the ContextKey works, let’s add in another model with its own ContextKey. I put this model in a separate project. The pattern for doing this when you have multiple models in the same project is a bit different; I’ll demonstrate that further along in this article. Here’s my new model, ModelTwo:

namespace ModelTwo.Context
{
  public class ModelTwoContext:DbContext
  {
    public DbSet<BBCEmployee> BbcEmployees { get; set; }
    public DbSet<HiringHistory> HiringHistories { get; set; }
  }
}

ModelTwoContext works with completely different domain classes. Here’s its DbConfiguration class, where I specified that the ContextKey be called ModelTwo:

internal sealed class Configuration : DbMigrationsConfiguration<ModelTwoContext>
  {
    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
      ContextKey = "ModelTwo";
    }

When I call update-database against the project that contains ModelTwoContext, the new tables are created in the same database and a new row is added to the __MigrationHistory table. This time the ContextKey value is ModelTwo, as you can see in the snippet of SQL that was run by the migration:

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201501132001186_InitialModelTwo', N'ModelTwo', [hash of the model], N'6.1.2-31219')

As I evolve my domain, my DbContext and my database, EF Migrations will always check back to the relevant set of executed migrations in the __MigrationHistory table using the appropriate ContextKey. That way it will be able to determine what changes to make to the database given the changes I made to the model. This allows EF to correctly manage the migrations for multiple DbContext models that are stored in the database. But remember, it’s only able to work because there’s no overlap with respect to the database tables to which the two models map.

Pattern Two: Database Schemas Separate Models and Migrations

The other pattern you can use to allow migrations to work with multiple models in a single database is to separate the migrations and the relevant tables with database schemas. This is as close to simply targeting separate databases as you can get, without some of the resource overhead (such as maintenance and expense) you might incur with multiple databases.

EF6 makes it much easier to define a database schema for a single model by configuring it with a new DbModelBuilder.HasSchema mapping. This will override the default schema name, which, for SQL Server, is dbo.

Remember that even if you don’t specify the context key, a default name will be used. So there’s no point in removing the context key properties I set to demonstrate how the HasSchema property affects migrations.

I’ll set the schema for each of my two context classes in the OnModelCreating method. Here’s the relevant code for ModelTwoContext, which I’ve specified to have a schema named ModelTwo:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {
    modelBuilder.HasDefaultSchema("ModelTwo");
  }

The other context will get the schema name ModelOne.

The result is that all of the database objects to which the Model­TwoContext maps will be in the ModelTwo schema. Additionally, EF will put the __MigrationHistory table for this model in the ModelTwo schema, as well.

To demo this in a clean way, I’m pointing to a different database than I did in the previous examples and applying all of the migrations. Keep in mind that setting the HasDefaultSchema method is a mapping change and requires that you add a new migration to apply that change to the database. Figure 1 shows the migration and data tables in their separate schemas.

Figure 1 Migrations and Tables Grouped into Database Schemas

Going forward, whenever you interact with migrations for either context, because they’re relegated to their individual schemas, EF won’t have a problem maintaining them separately. As a reminder, pay attention to the critical pattern here, which is that there’s no overlap with the tables mapped to the two models.

Multiple Models in a Single Project

The two examples you’ve seen thus far—using the ContextKey or the database schema to separate the model migrations—were set up with each model encapsulated in its own project. This is the way I prefer to architect my solutions. But it’s also possible and, in many cases, completely reasonable to have your models in the same project. Whether you use the ContextKey or the database schema to keep the migrations sorted out, you can achieve this with the addition of only a few extra parameters to the NuGet commands.

For clean separation of these examples, I’ll create a new solution with the same classes. I’ll keep the domain classes in separate projects, but both models in the same project, as shown in Figure 2.

Figure 2 Placing Multiple DbContext Classes in a Single Project

As you know, by default, enable-­migrations will create a folder called Migrations for a discovered DbContext in your solution. If you have multiple DbContexts as I do now, enable-migrations will not just randomly select a DbContext for creating migrations; instead, it will return a very helpful message instructing you to use the ContextTypeName parameter to indicate which DbContext to use. The message is so nice that you can just copy and paste from the message to run the necessary commands. Here’s the message returned for my project:

PM> enable-migrations
More than one context type was found in the assembly 'ModelOne.Context'.
To enable migrations for 'ModelOne.Context.ModelOneContext', use
 Enable-Migrations -ContextTypeName ModelOne.Context.ModelOneContext.
To enable migrations for 'ModelTwo.Context.ModelTwoContext', use
 Enable-Migrations -ContextTypeName ModelTwo.Context.ModelTwoContext.

In addition to the –ContextTypeName parameter, I’ll add in the MigrationsDirctory parameter to explicitly name the folder to make it easier for me to manage the project assets:

Enable-Migrations
-ContextTypeName ModelOne.Context.ModelOneContext
-MigrationsDirectory ModelOneMigrations

Figure 3 shows the new folders with their Configuration classes that were created for each migration.

Figure 3 Result of Specifying the DbContext and Directory Name When Enabling Migrations

Running Enable-Migrations also adds the code to the DbConfiguration classes, which makes them aware of the directory name. Here’s the configuration class for ModelOneContext as an example (the DbConfiguraton file for ModelTwoContext will set its directory name to ModelTwoMigrations, as designated):

internal sealed class Configuration : DbMigrationsConfiguration<ModelOne.Context.ModelOneContext>
  {
    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
      MigrationsDirectory = @"ModelOneMigrations";
    }

Because I now have two classes named Configuration, I’ll be forced to fully qualify them any time I want to use them. So I’ll rename the first to ModelOneDbConfig (as shown in the following code) and the second to ModelTwoDbConfig:

internal sealed class ModelOneDbConfig : DbMigrationsConfiguration<ModelOneContext>
  {
    public ModelOneDbConfig()
      {
        AutomaticMigrationsEnabled = false;
        MigrationsDirectory = @"ModelOneMigrations";
      }
  }

You can also specify a ContextKey if you want to override the default, but I’ll leave that alone. Remember that I did specify the HasDefaultSchema mapping method in my DbContext classes, so the migration history tables and other database objects will be housed in their own schemas.

Now it’s time to add migrations for both models and apply them to my database. Again, I have to direct EF to which model and migration to use. By pointing to the migration configuration file, EF will know which model to use and in which directory to store the migration files.

Here’s my command for adding a migration for ContextOne (remember that I changed the configuration class name so I don’t have to use its fully qualified name for ConfigurationTypeName):

add-migration Initial
  -ConfigurationTypeName ModelOneDbConfig

The resulting migration file gets created in the ModelOne­Migrations directory. After I do the same for ModelTwo, I also have a migration file in the ModelTwoMigrations directory.

Now it’s time to apply these migrations. I’ll need to specify the ConfigurationTypeName again so that EF knows which migration to use. Here’s the command for ModelOne:

update-database
  -ConfigurationTypeName ModelOneDbConfig

I’ll run that and then the relevant command for ModelTwo:

update-database
  -ConfigurationTypeName ModelTwoDbConfig

After running these commands, my database looks just the same as it did in Figure 1.

As I modify my models and add and apply migrations, I just need to remember to specify the correct configuration class as a parameter in each of the commands.

Nice Fit with Domain-Driven Design Modeling

In a recent two-part Data Points column called “A Pattern for Sharing Data Across Domain-Driven Design Bounded Contexts,” I wrote about sharing data across domains that are persisted to separate databases. Part One is at bit.ly/1wolxz2 and Part Two is at bit.ly/1817XNT. A number of developers have pointed out that maintaining a separate database on-premises can be a burden and paying for separate databases that are hosted in the cloud can be expensive. The techniques you learned in this article for hosting the tables and data for multiple models in a single database can help you to emulate complete database separation. This new support in EF6 migrations provides a nice solution for those developers.

---

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other.NET topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework” (2010), as well as a Code First edition (2011) and a DbContext edition (2012), all from O’Reilly Media. Follow her on Twitter at twitter.com/julielerman and see her Pluralsight courses at juliel.me/PS-Videos.

Thanks to the following Microsoft technical expert for reviewing this article: Rowan Miller