Code First Migrations

Working within the .NET Code First environment has many advantages. Code First gives you strongly typed classes that mirror the database environment because the actual database environment was built from these same strongly typed classes. For me the biggest advantage of Code First is that it writes and executes all of the database definition SQL for you. From creating the tables, to setting the primary keys, and establishing relationships between tables through foreign keys, Code First handles it all (or at least most of it). This is a HUGE time saver. Consider if you did it the “old fashioned” way by creating the tables, keys, etc. first using SQL, you would still end up creating strongly typed classes within your project. Code First saves you that hassle.

Joe already went over the model creation portion of Code First. This blog post will cover the database migration portion of Code First.

After you’ve created your model, you need to create a database migration. This can be done by utilizing the Package Manager Console within Visual Studio 12. The Package Manager Console can be accessed by going to “TOOLS > Library Package Manager > Package Manager Console”. After this console has opened, you can pin it to your work environment for easier access later. From the Package Manager Console (which is “PM>”), type “add-migration migration name” where migration name is the name you want to give to this particular migration. If this is the very first migration, something like “InitialDatabaseCreation” might be appropriate for the migration name. This command will create a C# class within the “Migrations” folder of your Model project. This class will have the name of your migration name and the class will contain two methods: “up” and “down”. The “up” method upgrades the database with your latest model changes. The “down” method can be used to rollback the migration’s changes after they have been committed.

The “up” method will contain code to create the various tables and keys based upon your entity model. A sample of the “up” method’s code is below.

The above example is creating a table called “DriverYears” which has a primary key on the “DriverYearId” field. The table also has four foreign keys to other tables and the “add-migration” command also added indices on those foreign key fields. The code above is a combination of C# and LINQ.

The “down” method contains code to undo any changes created by the “up” method. Most likely it will contain code to drop tables from the database. Below is a sample of the “down” method that was created using the “add-migration” command above.

You can see from the sample above that the “down” method will remove any database objects created by the migration’s “up” method.

If you were to connect to your database server at this point, you would not see a database pertaining to your project. The “add-migration” command only creates the migration; you still need to execute it using the “update-database” command. If you look at the C# migration code, you will not see any actual code to create the database. This is handled behind the scenes by the “update-database” command. The “update-database” command will look to see if the database used in your connection string exists on the database server. If there is no database with the name specified in the connection string, the “update-database” will create the database with all of the default values (database/log file name/location, language, settings, etc.). If you need to change any of these default values, you will either have to create the database manually or change these settings after the “update-database” command has been executed.

We are using the convention “project_(Local)” for the name of our database, where project is the name of your Visual Studio project. An example of the Derby project’s connection string is shown below. Using this convention makes it unnecessary for different developers to constantly having to change the connection string when getting the latest version of the project from the SVN.

From the prompt within the Package Manager Console, type “update-database” and hit “enter” (you can add the “-verbose” switch to see exactly what SQL scripts the “update-database” command is executing). This command will create the database (if necessary) and perform everything that is within any “up” methods for migrations that have yet to be executed. If you connect to your database server now, you will see your database with a table for every entity you created within your model.

Any changes to your model after the “update-database” command has been executed, will require the creation of another Code First database migration. Because you may have multiple developers working on the same project and making changes to the entity model, you may run into a case where your database is out of sync with the model. In fact, it may be several migrations behind the current model. You can see what state your database is in by looking at the system table “__MigrationHistory”. This table will show which Code First migrations have been executed against this database.

With multiple developers working on the same project, there can be instances where the Code First model believes that it is out of sync with the database. The Code First model will throw an error stating that a migration needs to be executed to bring the database in line with the model (need to get actual error from desktop). This can occur even if no changes to the Code First model have been made. This error can be frustrating because it prevents you from executing your project. You can try to alleviate this error by executing the “update-database” command. Sometimes, however, this will not solver the error. Another solution to try and fix this error is to completely delete the database and run “update-database”. This will usually fix the problem but it is not an ideal solution because you will lose any data you had in the database and thus have to add this lost data again. The best solution to prevent this error is to tell the Code First model not to check whether it is in sync with the database model. This can be accomplished by adding the following line to your “Configuration.cs” file.

An even better solution is to add a Boolean property the configuration file and wrap the above statement within a check of this property.

As was stated earlier, any changes to the Code First model after an “update-database” has been executed will require a new migration. It is a best practice to create new migrations consisting of the smallest logical change to the model. Name the new migration after the most logical thing that was changed and run the “update-database” command. Then make any additional model changes, create a new migration, and run the “update-database” command again. This will make it easier to see which migrations added what specific elements to the model and database.

There are certain database features where it might be easier to implement directly against the database instead of implementing through the Code First entity model. A few database features in which this may apply are indices, constraints, functions, and stored procedures. As was mentioned in the post about Code First model creation, views should be created directly on the database with a corresponding entity created within the Code First model. However, within the migration the C# code that would create a table based upon the view “entity” needs to be removed or commented out. The migration will try to create a table that corresponds to the view “entity” you’ve created in C#.

Overall Code First is an excellent way to create a data model and corresponding database with database objects. It has proven to be a big time saver on the tedious task of database creation and modification.