Introduction
Up to Entity Framework 5.0, there were only single migrations possible to manage a single DbContext (user model) per physical database. Now Entity Framework 6.0 supports multiple model migrations per physical database.
This feature of Entity Framework is formerly known as "Multi-Tenant Migrations" or "Multiple Contexts per Database".
Generally a multi-tenant database is able to isolate various groups of tables that might to be used for different purposes or may be used for different applications. There are multiple ways to make database multi-tenant. One of the common methods is to use a different schema name for a different group of tables.
Entity Framework Code First model migration allows us to create a new database or update the existing database based on model classes. Entity Framework 5.0 Code First migration only supports one DbContext per physical database. Entity Framework 6.0 onward, Code First migration supports multiple DbContexts per single physical database.
The following is the procedure to migrate a Code First model.
SyntaxStep 1
- enable-migrations -ContextTypeName -DbContext-Name-with-Namespaces-MigrationsDirectory:-Migrations-Directory-Name-
Step 2
- Add-Migration -configuration -DbContext-Migrations-Configuration-Class-with-Namespaces-Migrations-Name-
Step 3
- Update-Database -configuration -DbContext-Migrations-Configuration-Class-with-Namespaces -Verbose
Important NoteThe Connection String must be the same as the ADO.NET Connection String and also the name provided must be “System.Data.SqlClient” instead of “System.Data.EntityClient” that is used in a Database First model.
- <configuration>
- …..
- …..
- <connectionStrings>
- <add name="Entities" connectionString="Data Source=serverName;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=sa;Password=password ;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
- </connectionStrings>
- </configuration>
Example
Suppose I have two DbContexts (say EmployeeContext and OderContext) within the same project. The Model classes and DBContext class code is given below.
Model Classes (Employee context)
- public class DepartmentMaster
- {
- [Key]
- public int DepartmentId { get; set; }
- public string Code { get; set; }
- public string Name { get; set; }
- public List<EmployeeMaster> Employees { get; set; }
- }
-
- public class EmployeeMaster
- {
- [Key]
- public int Employee { get; set; }
- public string Code { get; set; }
- public string Name { get; set; }
- public int DepartmentId { get; set; }
- public DepartmentMaster Department { get; set; }
- }
Employee DbContext
- public class EmployeeContext : DbContext
- {
- public EmployeeContext()
- : base("name=Entities")
- {
-
- }
- public DbSet<DepartmentMaster> Departments { get; set; }
- public DbSet<EmployeeMaster> Employees { get; set; }
- }
Model Classes (Order context)
- public class OrderMaster
- {
- [Key]
- public int OrderId { get; set; }
- public DateTime OrderDate { get; set; }
- public double TotalAmount { get; set; }
- public List<OrderDetails> OrderDetails { get; set; }
- }
-
- public class OrderDetails
- {
- [Key]
- public int OrderDetailId { get; set; }
- public int OrderId { get; set; }
- public string ItemName { get; set; }
- public int Quantity { get; set; }
- public string UnitPrice { get; set; }
- public double Amount { get; set; }
- public OrderMaster Order { get; set; }
- }
Order context
- public class OrderContext : DbContext
- {
- public OrderContext()
- : base("name=Entities")
- {
-
- }
- public DbSet<OrderMaster> Orders { get; set; }
- public DbSet<OrderDetails> OrderDetails { get; set; }
- }
Graphical representation of relationship
Code First migration ProcedureFor migrating, the following commands must run using the Package Manager console of Visual Studio (2012 or 2013).
Step 1
- Enable migration for DbContext
enable-migrations -ContextTypeName MultipleContextsperDatabase.Model.EmployeeContext -MigrationsDirectory:EmployeeMigrations
Output on Package Manager ConsoleAs a result of Step 1, the Configuration class is added within the EmployeeMigration folder.
Configuration class definition
- internal sealed class Configuration : DbMigrationsConfiguration<MultipleContextsperDatabase.Model.EmployeeContext>
- {
- public Configuration()
- {
- AutomaticMigrationsEnabled = false;
- MigrationsDirectory = @"EmployeeMigrations";
- }
-
- protected override void Seed(MultipleContextsperDatabase.Model.EmployeeContext context)
- {
-
- }
- }
Step 2: Add migration configuration
Add-Migration -configuration MultipleContextsperDatabase.EmployeeMigrations.Configuration InitialEmployee
Output on Package Manager Console
As a result of Step 2, the Db migration file is generated within the “EmployeeMigration” folder. This file has a name suffix as the Migrations name followed by underscore and a unique generated number. This file has all the entities to be created in the database.
Migration file Definition
- public partial class InitialEmployee : DbMigration
- {
- public override void Up()
- {
- CreateTable(
- "dbo.DepartmentMasters",
- c => new
- {
- DepartmentId = c.Int(nullable: false, identity: true),
- Code = c.String(),
- Name = c.String(),
- })
- .PrimaryKey(t => t.DepartmentId);
-
- CreateTable(
- "dbo.EmployeeMasters",
- c => new
- {
- Employee = c.Int(nullable: false, identity: true),
- Code = c.String(),
- Name = c.String(),
- DepartmentId = c.Int(nullable: false),
- })
- .PrimaryKey(t => t.Employee)
- .ForeignKey("dbo.DepartmentMasters", t => t.DepartmentId, cascadeDelete: true)
- .Index(t => t.DepartmentId);
-
- }
-
- public override void Down()
- {
- DropForeignKey("dbo.EmployeeMasters", "DepartmentId", "dbo.DepartmentMasters");
- DropIndex("dbo.EmployeeMasters", new[] { "DepartmentId" });
- DropTable("dbo.EmployeeMasters");
- DropTable("dbo.DepartmentMasters");
- }
- }
Step 3: Update database
Update-Database -configuration:MultipleContextsperDatabase.EmployeeMigrations.Configuration -Verbose
Output on Package Manager Console
After executing the command above a new something will be created (or update the existing database) that is supplied in the initial catalog property of the Connection String.
A migration history table is a table that stores the details of migrations applied to the database in a Code First model. It means that the migration history table contains all the migration changes to the database. The default name of this table is _MigrationHistory and it is created when a first migration is applied to the database. In Entity Framework 5.0, this is a system table (If application uses MSSQL). In the Entity Framework 6.0, this table is no longer marked as a system table.
Output of migration history table
The same procedure as described above must be followed for OderDbContext.
The following command must be run one by one by using the Package Manager console of Visual Studio (2012 or 2013).
enable-migrations -ContextTypeName:MultipleContextsperDatabase.Model.OrderContext -MigrationsDirectory:OrderMigrations
Add-Migration -configuration MultipleContextsperDatabase.OrderMigrations.Configuration InitialOrder
Update-Database -configuration:MultipleContextsperDatabase.OrderMigrations.Configuration -Verbose
Configuration class definition (generated by Step-1)
- internal sealed class Configuration : DbMigrationsConfiguration<MultipleContextsperDatabase.Model.OrderContext>
- {
- public Configuration()
- {
- AutomaticMigrationsEnabled = false;
- MigrationsDirectory = @"OrderMigrations";
- }
-
- protected override void Seed(MultipleContextsperDatabase.Model.OrderContext context)
- {
-
- }
- }
Migrations file Definition (Generated by Step-2)
- public partial class InitialOrder : DbMigration
- {
- public override void Up()
- {
- CreateTable(
- "dbo.OrderDetails",
- c => new
- {
- OrderDetailId = c.Int(nullable: false, identity: true),
- OrderId = c.Int(nullable: false),
- ItemName = c.String(),
- Quantity = c.Int(nullable: false),
- UnitPrice = c.String(),
- Amount = c.Double(nullable: false),
- })
- .PrimaryKey(t => t.OrderDetailId)
- .ForeignKey("dbo.OrderMasters", t => t.OrderId, cascadeDelete: true)
- .Index(t => t.OrderId);
-
- CreateTable(
- "dbo.OrderMasters",
- c => new
- {
- OrderId = c.Int(nullable: false, identity: true),
- OrderDate = c.DateTime(nullable: false),
- TotalAmount = c.Double(nullable: false),
- })
- .PrimaryKey(t => t.OrderId);
-
- }
-
- public override void Down()
- {
- DropForeignKey("dbo.OrderDetails", "OrderId", "dbo.OrderMasters");
- DropIndex("dbo.OrderDetails", new[] { "OrderId" });
- DropTable("dbo.OrderMasters");
- DropTable("dbo.OrderDetails");
- }
- }
Table generate by the step-3
Output of migration history table
Undo/Rollback DbContexts MigrationsWe can also roll back database changes using the following command.
Update-Database -configuration MultipleContextsperDatabase.EmployeeMigrations.Configuration -TargetMigration:"201407221118333_InitialEmployee" –verbose
Update-Database -configuration MultipleContextsperDatabase.OrderMigrations.Configuration -TargetMigration:"201407221128534_InitialOrder" -verbose
Output on Package Manager Console
Summary
Entity Framework has many new features, Multi-Tenant migration is one of them. With this feature we can get the same functionality as a multi-tenant database. I hope this will help you.