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:

Updated schemas through update database entity framework

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.

Advertisements

About Andras Nemes
I'm a .NET/Java developer living and working in Stockholm, Sweden.

4 Responses to Introduction to EntityFramework 6 Part 5: updating schemas

  1. Luc says:

    Thanks for sharing Andras. Liked the series a lot.

  2. Srinivas says:

    Great post.

  3. Cesar says:

    Changing the schema for a table doesn’t rename the PK or FK names for each table being moved. When you are later dealilng with those keys, EF will take the name it “assumes” it SHOULD have, but not the one they actually HAVE.
    Beware of this! In my work we have already reproduced this behavior. Dunno yet if there’s some direct solution other than modifying things on your own on the migration.

  4. Great!

    But i need to change the schema and have the tables on both.
    Is there any way for this?createtable instead of movetable?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

ultimatemindsettoday

A great WordPress.com site

Elliot Balynn's Blog

A directory of wonderful thoughts

Robin Sedlaczek's Blog

Developer on Microsoft Technologies

HarsH ReaLiTy

My goal with this blog is to offend everyone in the world at least once with my words… so no one has a reason to have a heightened sense of themselves. We are all ignorant, we are all found wanting, we are all bad people sometimes.

Softwarearchitektur in der Praxis

Wissenswertes zu Webentwicklung, Domain-Driven Design und Microservices

the software architecture

thoughts, ideas, diagrams,enterprise code, design pattern , solution designs

Technology Talks

on Microsoft technologies, Web, Android and others

Software Engineering

Web development

Disparate Opinions

Various tidbits

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Cyber Matters

Bite-size insight on Cyber Security for the not too technical.

Guru N Guns's

OneSolution To dOTnET.

Johnny Zraiby

Measuring programming progress by lines of code is like measuring aircraft building progress by weight.

%d bloggers like this: