Introduction to EntityFramework 6 Part 5: updating schemas

Introduction

In the previous part of this series we looked at asynchronous DB calls and logging in EF 6. In this finishing post we’ll take a look at schemas.

If you create a database through code-first in EntityFramework then the new tables will be automatically put into the dbo schema: dbo.Customers, dbo.Orders etc. That is the case with our demo application where all tables, i.e. Cars and the ones related to identity belong to the dbo schema.

However, there are times that you’d like group your tables into different schemas depending on their purpose, domain or some other criteria.

Migrating from one schema to another in EF code-first is quite easy. We’ll apply it to our demo Cars application so have it ready in Visual Studio.

Demo

The goal is to move the domain related tables to the ‘domain’ schema and the identity related tables to the ‘identity’ schema. The solution is that we need to override the OnModelCreating method in the Db context class(es). Open CarsDbContext and add the following method just above the Cars property:

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

Similarly, add the following method to UserManagementDbContext:

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

Save and build the solution so that the changes are properly registered.

Our domain and identity models have just got extended so the underlying database and the model are out of sync. That means we’ll need to perform a migration like in part 2 of this series. Open the Package Manager Console – View, Other Windows, Package Manager Console – and run the following command:

Add-Migration -ConfigurationTypeName Cars.Web.Database.DomainMigrations.Configuration “SettingNewSchema”

This creates a new migration file called somedatestamp_SettingNewSchema.cs with the following contents:

public partial class SettingNewSchema : DbMigration
{
        public override void Up()
        {
            MoveTable(name: "dbo.Cars", newSchema: "domain");
        }
        
        public override void Down()
        {
            MoveTable(name: "domain.Cars", newSchema: "dbo");
        }
}

So we’re simply moving the table(s) from the dbo schema to domain.

Let’s do the same for out identity tables. Run the following command in the package manager:

Add-Migration -ConfigurationTypeName Cars.Web.Database.UserManagementMigrations.Configuration “SettingNewSchema”

You’ll get a file similar to the one above but with a MoveTable statement for each identity table. Next we need to run the SettingNewSchema scripts. Run the following commands in the package manager:

Update-Database -ConfigurationTypeName Cars.Web.Database.UserManagementMigrations.Configuration
Update-Database -ConfigurationTypeName Cars.Web.Database.DomainMigrations.Configuration

Open the DB table structure in Server Explorer and you’ll see the new schema names:

Run the application and test viewing, adding and updating the car objects. It should all work like it did before.

This post finishes the introductory series on EF 6. You can view all posts related to data storage on this blog here.