Digging in to Multi-Tenant Migrations with EF6 Alpha

from: http://thedatafarm.com/blog/data-access/digging-in-to-multi-tenant-migrations-with-ef6-alpha/

I wanted to give the new Multi-Tenant migration feature in the EF6 alpha (released Oct 30, 2012) a whirl and having done so, thought I would save someone a few steps figuring it out. The specs are very useful and thankfully I’m fairly familiar with migrations already so I didn’t have much difficulty getting it to work.

First, Some Caveats and Explanations

I’d like to start with a few caveats though. And for those of you who aren’t familiar with multi-tenant databases, a very brief explanation.

A multi-tenant database is able to isolate different groups of tables that are to be used for different purposes – most typically different applications. One method of doing this is commonly done with schemas e.g.: [JuliesApp].Customers, [JuliesApp].Addresses, [JuliesApp].Employees, [FransApp].SoftwareProducts, [FransApp].SoftwareVersions, [FransApp].Customers. (Note that those are [Schema].TableName, not just table names with a period in the middle.) I’ve got clients with databases that use multi-tenancy, so I am very interested in this feature.

Adding in Ido's explanation from the comments(since there are multiple ways people use MT databases)

"hosting multiple clients on the same database-application server pair thus reduce overhead of deployment, maintenance, cost and some others."

Here's an article on MSDN that's much more authoritative on the topic of what & why than I can ever be.  Multi-Tenant Data Architecture

Up through EF5, Code First lets you specify a schema for each entity mapping using the ToTable/[Table()] configuration where you could add a schema name along with the table name. This required explicitly setting the table each time. Now with EF6, you can specify a schema per model in the OnModelCreate override with ModelBuilder.HasDefaultSchema(“People”). So if you are defining your models to represent a multi-tenant database, it is now simpler to specify the schema for all of the tables that are mapped to by your model’s entities.

Here is where I want to make first caveat. I’ve been talking and writing a lot lately about DDD Bounded Contexts and having multiple models in your domain. With my Bounded Context models, there can be a lot of overlap in mappings to the database. This is not where I would use HasDefaultSchema. With the pattern for creating multiple models for Bounded Contexts in a single app, I have also suggested a single context to be used for all database initialization. I’m not going to go into more detail on that but I just wanted to be sure that those of you who have seen my Pluralsight course, Entity Framework in the Enterprise, or my January 2013 MSDN Data Points column — if you are reading this after that’s been published — not to mistake my mention of “multiple models for multi-tenancy” for “multiple models for Bounded Contexts”.

Once you’ve defined various schemas, in EF5 and earlier, Code First migrations does not support this multi-tenancy. That’s changed in EF6. The team has added in the ability to create and execute migrations per model. And in order to do so they’ve changed some of the workflow for the metadata history tracked by the database. This feature is already available in the early EF6 alpha that was released at the end of October. You can read the specs on the EF CodePlex site.

Here is the second caveat. If you have seen Rowan Miller’s blog post about multi-tenancy with EF 4.1 Code First, that is focused on a particular scenario—repeating the same model in a database with different schemas. Here’s a screenshot from his blog post that explains what I mean:

I actually spent quite a lot of time with the new migrations feature to try to follow this path and since it’s an edge case, it’s not (yet?) readily supported. I eventually found a way to do it but it was pretty convoluted and I won’t bother sharing it here.

Trying out the Feature: First we need an M-T Database

I’m starting with two simple solutions where I’ve already defined my classes, model and a little console app to exercise them. They will both use the same database.

The Models:

public class HotelRoomsModel : DbContext
{
  public DbSet<Hotel> Hotels { get; set; }
  public DbSet<Room> Rooms { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.HasDefaultSchema("Hotel");
    base.OnModelCreating(modelBuilder);
  }
}

 public class CasinoSlotsModel : DbContext
 {
   public DbSet<Casino> Casinos { get; set; }
   public DbSet<SlotMachine> SlotMachines { get; set; }
   public DbSet<PokerTable> PokerTables { get; set; }
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
     modelBuilder.HasDefaultSchema("Casino");
     base.OnModelCreating(modelBuilder);
   }
 }

Now I’ll initialize the model in the Casino app with some simple code:

  using (var context = new CasinoSlotsModel())
  {
    context.Database.Initialize(force: true);
  }

And my database gets created.

If I initialize the second model using similar code in the HotelApp, I get a second database: DataLayer.HotelRoomsModel. Not what I wanted. That’s because I’m using defaults.

Point Both Models to the Same Database

I need to use one of the many ways to direct both models to the same database. I’ll keep it simple for now and do that in the constructor for both model classes.

 public class HotelRoomsModel : DbContext
 {
   public HotelRoomsModel(): base("CasinoHotels")
   {      }

 public class CasinoSlotsModel : DbContext
 {
   public CasinoSlotsModel():base("CasinoHotels")
   {    }

Now I’ll initialize both models again.

Just what I wanted! Notice that I am using the default initialization still which for Code First is CreateDatabaseIfNotExists. I have not switched to migrations yet. So when it initialized the first model, no problem..there was no database yet so it created it. When it initialized the second model, the database already existed, but EF6 understood what to do since it was a separate tenant in the database.

If I had a type called Room in my Casino app, I’d also have a Casino.Rooms table in the database. The two apps know which tables are theirs so it doesn’t pose a problem.

Notice also that under System.Tables, I have some new tables that Code First uses to track the metadata from both app – or really for both schemas:

This is an important change in EF6.

Modifying something in the model and Initializing again – A Surprise

There are many ways to modify a model. You can add another DbSet to the model. You can change one of the classes that the model exposes. You can modify how the classes map to the database. I’ll do a quick mod – add a property to the Room class in the Hotel app. If I run the intialization again, EF should see that the model is already represented in the database, but in it’s former definition. Because I’ve changed the model, I anticipate that EF will suggest that I use migrations along with the big fat exception it will throw. (This is just how it’s been since EF 4.3.) But it didn’t! It just initalized happily without reporting a problem and the new property is completely ignored by Code First. That will end up with a runtime surprise down the road when I try to query or update using the new property.

I did some experiments to verify that the HasDefaultSchema is responsible for this. I should probably look to see if this is logged in Issues yet. :)

Update 11/19: Andrew Peters from the EF team confirmed the problem and has added this work item to correct it.

Enabling Migrations for *both* tenant apps

I’ll just skip ahead and switch to migrations anyway. If you’ve used migrations this will be familiar; but there are a few twists.

I have two projects with their own models in the, so I need to enable migrations in both projects.

In the package manager console window of each solution, I’ll enable migrations for the project with models in them:

PM>enable-migrations -ProjectName:DataLayer.CasinoModel -MigrationsDirectory:CasinoMigrations 

PM>enable-migrations -ProjectName:DataLayer.HotelModel -MigrationsDirectory:HotelMigrations 

Since I don’t have multiple models in a single project (I recommend against doing that anyway), I don’t believe that I really need to use the new MigrationsDirectory parameter. But I’m trying out the new features so in it goes! What this does is name the folder with the name I provided (e.g. CasinoMigrationsl or HotelMigrations) instead of just “Migrations”.

It also adds the MigrationsDirectory setting in the Configuration class. You can read more about MigrationsDirectory in the specs.

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
      MigrationsDirectory = @"CasinoMigrations";
    }

A Configuration Kludge that May only be needed because of a Bug in the Alpha Bits

When the CreateDatabaseIfNotExists default initializer created the database, it created one MigrationHistory table for each model I was tracking. Those tables contain a [new to EF6] field called ContextKey. By default, Code First uses the strongly-typed name of the context for that ContextKey value in each row added to the table. When I switched to Migrations, it was looking for a context key of the schema name (that seems to be expected behavior). However, my interpretation of the specs was that Code First would be able to deal with this change, but it didn’t seem to be. Not finding a MigrationHistory table that contained any rows with “Hotel” as the context key, Migrations tried to create a new Hotel._MigrationHistory table. That failed because it already existed. My workaround (for now) was to explicitly set the context key to use the old default.

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
      MigrationsDirectory = @"HotelMigrations";
      ContextKey = "DataLayer.HotelRoomsModel";
    }

I did the same for the Casino Migration Configuration file setting ContextKey to “DataLayer.CasinoSlotsModel”.

I don’t know if I misunderstood what I read in the specs or if there’s a bug. So I’ll keep an eye on that one with further releases.

Update 11/19: Andrew Peters from the EF team has confirmed this problem and created this work item to fix it.

Use Code-Based Migrations, not Automatic

I set AutomaticMigrationsEnabled=true (and told Code First I was using the Migrations Initializer) for the HotelRoomsModel to see what would happen as a result of adding a new property to the Room class. I got the following error message when I tried to initialize the context for that model.

So you need to explicitly create and execute migrations for each model as needed in the Package Manager Console Window.

Let’s see how that goes. Remember I’ve added a new property to the Room class since I initialized the database.

PM> add-migration NewPropertyInRoomType -ProjectName:DataLayer.HotelModel 

This creates a new migration named “NewPropertyInRoomType” for my HotelModel project.

And the migration recognized the correct change to the model:

  public partial class NewPropertyInRoomType : DbMigration
  {
    public override void Up()
    {
      AddColumn("Hotel.Rooms", "FirstNewPropertyWithMigrations", c => c.String());
    }
        
    public override void Down()
    {
      DropColumn("Hotel.Rooms", "FirstNewPropertyWithMigrations");
    }
  }

Now I have to explicitly execute this using update-database but making sure again that the command is executed for my HotelModel project. (FWIW, the PackageManager Console windows has a drop down to select the appropriate project to execute each command in but I’ve found that I prefer to just do that in code rather than in the UI.)

PM> update-database -ProjectName:DataLayer.HotelModel  -verbose

I like to use –verbose so I can see what’s happening. :)

I’ve got the new property in my database table now.

Additionally, I can see that the migration and representation of the current model were added as a new row into the correct MigrationHistory table: Hotel.__MigrationHistory.

I’m satisfied for now. I actually spent a long time on this because I hit walls, had to back up, explore different behavior, go back and read the specs repeatedly, etc. But hey, a girl’s gotta have some fun, right? :) (yes this is indeed fun for me…when I have a little free time on my hands. :) )

Check it Out Yourself and Give Feedback to the Team

So if you are interested in multi-tenant database support, please check out the feature and provide feedback either in the Issues or the Specs page for this feature. This is an early alpha and you can help shape how it works when EF6 releases.

*Thanks to Frans Bouma for pointing out that I hadn’t clearly divided this into two app on my first pass. I took the post off-line for about 1/2 hour to make the revision.