Indexes in Code-Based EF Migrations

In the Adding Indexes with EF Migrations post I showed how to add indexes to a database created by EF Code First with a separate migration step. When using code-based migrations to add a table, there is another, better way to add indexes, if it is done together with the table. If you’ve read my Type Safe SelectList Factory post you know my feelings about code that contains class or property names as strings. I was considering writing a similar post about indexes in EF Migrations until I found out that there is a built in index creation, using lambdas.

Adding Indexes with EF Migrations

Most business applications use a database. Creating the database in the first place is the easy part. Continously evolving the database schema during development when multiple team members are doing changes in various parts of the database is one of the hard parts. The really hard part is to be able to upgrade – or revert back to – a specific version of the schema when needed.

In the first post in this series I created a basic database with the EF Code First features. One of the things I couldn’t do there was creating an index, so in this post I will look into adding an index to my database through EF Migrations in Entity Framework 4.3.

This post describes how to add an index separately. If the index is created together with there is a better way, as described in Indexes in Code-Based EF Migrations.

Using Entity Framework to Create a Database

It’s been a few years since I worked with Entity Framework and there have been substantial improvements. Still I have been missing a life cycle view on the database, where the evolution of a database schema during development and maintenance is fully supported. The latest Entity Framework 4.3 release contains EF Migrations which claims to solve the problem. In a series of post I will explore EF Migrations and see if it can do what I want. In this post I will focus on getting Entity Framework to create a database with a decent schema for me. If I succeed I will continue with the migrations tools to see if they are powerful enough for what I want. For the database I want to have a schema that contains the following features:

  1. Database generated int identity(1,1) primary keys.
  2. Foreign Keys
  3. Custom string length constraints.
  4. Indexes
  5. Table mapped to an enum in code.

Database Table Primary Keys

When designing a database, I have a standard of always creating a clustered primary key in each table of type INT IDENTITY(1,1) NOT NULL.

CREATE TABLE Cars(
    ID                    INT IDENTITY(1,1)   NOT NULL
    CONSTRAINT PK_Cars PRIMARY KEY CLUSTERED,
    Brand                 NVARCHAR(20)        NOT NULL,
    RegistrationNumber    NVARCHAR(10)        NOT NULL,
    MadeIn                NVARCHAR(20)        NOT NULL
)

It is simple to create and for 90% of all tables this will give good performance and a small storage footprint. Those 9% of tables that are link tables for many-to-many relationships get a composite primary key, made up of the primary keys from the two related tables. Two simple rules handles 99% of the cases for primary keys. In this post I will go through some considerations for primary keys and show why an INT IDENTITY(1,1) NOT NULL field almost always is a good choice for a clustered primary key. The examples and technical details in this article are written for MS SQL Server, but the concepts should apply to other database engines as well.

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

Powered by WordPress with the Passion for Coding theme.