Adventures with Entity Framework: Setting up for easy schema changes

I had an interesting ask the other day I've never had to deal with before. Change the default schema for all tables using Entity Framework on a deployed production database. There were of course a couple hiccups along the way so I thought Id share what I learnt.

Its important to set yourself up for success right from the beginning so one of the things I always do is define the default schema. Even if you have no intention of ever changing it down the line you never know where AGILE will take you.

In your database context simply set the default schema when you override the OnModelCreating. Doing this right from the start allows you to use this default schema in your entity configurations. That's a big win. In the future all you have to do to change your schema is alter one line of code and add a migration. Entity Framework will take care of the rest.

Below you can see how the default schema is being set when defining the table.

Because we have structured our project to use a default schema and defining that schema with configurations actually changing the default schema in the future becomes trivial.

Change the default schema

Create a new migration : add-migration schema-changed-to-library

If you have a fairly simple database schema that should be it, just generate the migration. Entity Framework will handle it all for you! I did run into one pitfall however, in a complicated deployed database. Triggers. Grrrrrrr. For whatever reason when Entity Framework generates a migration the triggers get forgotten. Or rather they are not renamed properly. Pro tip of the day: DONT FORGET THE TRIGGERS.

Update database and you are all good to go!

If you refactor your context out into a different project

We inherit projects a lot, its part of the business. They weren't necessarily setup the way you would prefer. Often you find yourself refactoring the solution and moving all the database related code out into its own project. If you've gone through this pain before this is something you wont ever forget again. In the __MigrationHistory table you have to update the ContextKey. This one has caused me a lot of head scratching in the past. Hopefully this helps one of you.

Ive packaged all these concepts in a github repo. Enjoy!

https://github.com/shawnrmoss/entity-framework-schema