Enums and Lookup Tables with EF Code First

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.

Update-Database MSI Custom Action

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.

EF Migrations Command Reference

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.

  • Enable-Migrations: Enables Code First Migrations in a project.
  • Add-Migration: Scaffolds a migration script for any pending model changes.
  • Update-Database: Applies any pending migrations to the database.
  • Get-Migrations: Displays the migrations that have been applied to the target database.

Prevent EF Migrations from Creating or Changing the Database

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.

EF Migrations and a Merge Conflict

To put a bit more stress on EF Migrations I’ll simulate a scenario that should be quite common in a multi developer environment.

  1. Developer A and B gets the latest code from the repository.
  2. Developer A adds a column and checks in a migration.
  3. 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.

Software Development is a Job – Coding is a Passion

I'm Anders Abel, a systems architect and developer working for Kentor in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

The complete code for all posts is available on GitHub.

Popular Posts

Archives

Series