Multi-Tenant Migrations With Entity Framework 6.0 (Code First Model)

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 the 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.

 

 

Syntax

 

Step 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 Note

 

The 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 the relationship

 

 

Code First migration Procedure

 

For 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 Console

 

 

As 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 an 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 the 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 Migrations

 

We 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.