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.

A First Database Schema

Following a walkthrough I created a simple model and a context which is outlined below.

public class Brand
{
    public int BrandId { get; private set; }
    public string Name { get; set; }
    public ICollection<Car> Cars { get; set; }
}
 
public class Car
{
    public int CarId { get; private set; }
    public Brand Brand { get; set; }
    public string RegistrationNumber { get; set; }
}
 
public class CarsContext : DbContext
{
    public DbSet<Car> Cars { get; set; }
    public DbSet<Brand> Brands { get; set; }
}

When the code was tested, it created the database automatically for me. There are two simple tables. Some things worked out of the box, like the automatically discovering a key and creating an identity column for it. The Ids looks exactly like I want them. The rest of the fields however are not good enough. I don’t want nvarchar(max) for my strings and I don’t like the name of the foreign key field generated.

Adding Data Annotation Attributes

To get better string lengths and a better name for the relation I added data annotation attributes to the code.

public class Brand
{
    public int BrandId { get; private set; }
 
    [Required]
    [StringLength(20)]
    public string Name { get; set; }
 
    public ICollection<Car> Cars { get; set; }
}
 
public class Car
{
    public int CarId { get; private set; }
 
    [ForeignKey("Car_Brand")]
    public Brand Brand { get; set; }
 
    [ForeignKey("Brand")]
    public int BrandId { get; private set; }
 
    [Required]
    [StringLength(6)]
    public string RegistrationNumber { get; set; }
}
 
public class CarsContext : DbContext
{
    public DbSet<Car> Cars { get; set; }
    public DbSet<Brand> Brands { get; set; }
}

Running the code again and looking in the database it looks better. The columns have correct data types and names. To get here I had to be a bit more clear and create both the navigation property and the foreign key field for the relation, but I think that it is just fine.

So far three of the five features I wanted are in place. The database generated primary keys are correct, the foreign key is correct and the string lengths are correct.  The remaining features are a custom index and an enum mapped to a table. I’ll start with the index.

Adding an Index

In my imaginary system there will be plenty of searches of cars of a specific brand. This requires an index on the BrandId column. I would also like to include the RegistrationNumber column in the index.

Unfortunately it cannot be done directly with attributes. There is a MS Connect request that can be voted for.

A workaround that looks promising is the new EF Migrations functionality, which I investigate in the next post in this series.

Adding a Table Mapped to an Enum

Adding a table mapped to an enum is another feature I would like to have. To be honest, my favourite data access tool LINQ to SQL doesn’t have that either (at least not in the code generation tools offered), but I think that it should really be there.

Unfortunately EF 4.3 is a disappointment in this area too. According to an ADO.NET blog post enums will be fully supported in EF 5.0 which will be shipped together with .NET 4.5. Enum support apparently requires changes to the .NET framework (why?) so they cannot add it until the next .NET framework release.

Conclusion

My tests so far shows that Entity Framework Code First isn’t the silver bullet to data access, but it is powerful enough to consider. The lack of enum support is a minor disappointment. The lack of integrated index and constraint handling through attributes could be a show stopper for me, unless EF Migrations can solve the problem. In the next post in this series I will investigate how EF Migrations handles indexes.