With EntityFramework’s support for enums, there is no longer any need to include lookup tables in the model. But I do want to have them in the database for integrity, even with code first.
I’ve been thinking for some time about to handle enums with code first. The idea behind code first is to be able to write the code as close as possible to how object oriented code is normally written. For enums that means that the enum definition itself is the constraint that is used to ensure that only valid values are used in the code.
For databases it would be possible to use a column constraint, but the normal way is to use a lookup table where the valid values are present. Any column in the database mapped against the enum type is then a foreign key to the lookup table to ensure integrity of the data.
What I would prefer is a solution where the lookup table is present in the database, but not mapped against any entity in the code.
In the Prevent EF Migrations from Creating or Changing the Database post I showed how to prevent the application from automatically creating or updating the database. Instead I want the installation program to do that. With a Web Setup Project for the installation an MSI Custom Action is needed.
The actual work of updating the database is done by the
migrate.exe tool. To make the MSI package run it properly turned out to be a bit of a challenge. I first included
migrate.exe in the installation package to have it deployed to the
bin directory together with the assemblies of the system. There is support for running an exe file as a custom action in the web setup projects. Ufortunately I couldn’t get
migrate.exe to work unless the working directory was set to the
bin directory. The working directory for custom actions is
c:\windows\system32 by default. To handle that, a small vb-script was used.
Entity Framework Migrations are handled from the package manager console in Visual Studio. The usage is shown in various tutorials, but I haven’t found a complete list of the commands available and their usage, so I created my own. There are four available commands.
One of the features of Entity Framework Code First is to automatically create the database on first access. It is a convenient approach in many cases. Unfortunately the applications I work with does not fall into one of those cases. The applications I develop typically run on a dedicated web server, with the database hosted on in a shared MSSQL environment somewhere nearby.
The shared MSSQL environment is often set up using advanced clustering and virtualization technologies to maximise performance and reliability. In such an enviromnent my code seldom have access rights enough to create a database and even if it did, automatically created databases is not an option. Databases should be created by a DBA that carefully chooses the right name of the database, the right place for data and transaction log files and applies the right growth settings for the files.
To work with the shared MSSQL environment the application shouldn’t to any automatic creation or updates to the database schema. On startup I only want the application to check two things:
- that the database exists
- that the database is compatible with the model in the code.
Only after those checks have passed I want the application to run. A custom database initalization strategy can do those checks and throw exceptions that prevent the application from running if there is no existing and compatible database.
To put a bit more stress on EF Migrations I’ll simulate a scenario that should be quite common in a multi developer environment.
- Developer A and B gets the latest code from the repository.
- Developer A adds a column and checks in a migration.
- Developer B adds another column and makes everything ready for checkin. During checkin Deveveloper B finds out about Developer A’s checkin, gets the latest version and then checks in.
When working in a team this is a common scenario. Especially in green field development where the database continously is expanded as more and more features are added.