Preexisting Schemata Relational Mappings Using Entity Framework 4.1 (Code First)

Lately I’ve been experimenting with the newest revision of Microsoft’s Entity Framework (version 4.1 and codenamed “Code First”) in a recent project of mine. Using it has been an interesting experience, and I must express my approval for the design methodology it promotes and makes possible: the ability to create your data model in your code first, and generate an effective database from that. This is the inverse of the more common scenario we see today, which is to have a database first, and then create POCOs from that, more or less. The usefulness of this approach is something that can be debated about some other time, however.

The project I’ve been working on already had a defined (legacy) database schema, therefore my use of the Entity Framework would not concern the generation of new schema from a new data model, but instead had to make use of a schema that did not conform to their corresponding POCOs. There have been a few surprises and stumbling blocks I’ve encountered while working with the framework, and I would like to cover one or more of these stumbling blocks in this post; specifically, I’d like to cover the use of related entities in a class, and how to map the class’s one-to-many relationship with these entities, among other things.

So let’s look at how you would establish the required relational mappings with your model builder when dealing with classes that contain one-to-many references to other entity types, all involving the use of a legacy database.

What We are Working With

Let’s assume we have the following fictitious and very sparse-looking POCO classes:

    public class ResourceReservation
    {
      public int Id { get; set; }

      public DateTime Start { get; set; }

      public DateTime End { get; set; }

      public Resource ReservedResource { get; set; }

      //...other properties go here...
    }

    public class Resource
    {
      public Resource()
      {
        ResourceReservations = new ObservableCollection<ResourceReservation>();
      }

      public int Id { get; set; }

      public string Name { get; set; }

      public virtual ICollection<ResourceReservation> ResourceReservations { get; private set; }

      //...other properties go here...
    }

So we have two entity types here. Before deciding how we map these tables and the relationship between them, we need to look at the preexisting database. So, here is a snapshot of how these tables are defined in this fictitious database:

As pictured above, we can see a very legacy-looking database, using naming conventions for columns and tables that don’t match up with our POCOs. We can also see that the relationship between the two tables is established via the foreign key that exists within the resourcereservation table, which has been given the lovely name of ‘FKresourcereservationresource’.

When using the Entity Framework as our ORM, it will have certain expectations as to what corresponding entities within the database will be named. This applies to tables, columns, and even foreign keys. So manual specification for all of these items will be required.

Mapping Enhancements

All of the code we’ll be dealing with in order to do this will exist within the OnModelCreating override found in our class that derives fromDbContext. Mapping properties to columns by the column name looks something like the following:

    modelBuilder.Entity<Resource>().Property(rr => rr.Start).HasColumnName("res_start_dt");

    modelBuilder.Entity<Resource>().Property(rr => rr.End).HasColumnName("res_end_dt");

This is sort of clumsy to work with — plus, we are unable to use fluent syntax here; we need to issue separate statements for each mapping, similar to what is shown above. I point that out because I do believe that a fluent syntax was possible in previous versions of the “Code First” iteration of the Entity Framework, namely CTP5, perhaps CTP4?

Using the following extension methods can alleviate some of the pain and allow for fluent syntax:

    /// <summary>
    /// Maps the property specified by the provided property expression to the column whose name matches the specified column name.
    /// </summary>
    /// <typeparam name="TEntityType">The type to be registered or configured.</typeparam>
    /// <typeparam name="TValueType">The value type of the property.</typeparam>
    /// <param name="self">The non-null type of entity that is having a property mapped to it.</param>
    /// <param name="propertyExpression">The expression that declares the property being mapped onto the entity type.</param>
    /// <param name="columnName">The name of the database column the property corresponds to.</param>
    /// <returns>The object this method extends, allowing for a more fluent mapping syntax.</returns>
    /// <remarks>
    /// This is a convenience method. This should be removed if the official EF framework is released with better property -> column
    /// mapping methods.
    /// </remarks>
    public static EntityTypeConfiguration<TEntityType> MapByColumnName<TEntityType, TValueType>(
        this EntityTypeConfiguration<TEntityType> self, Expression<Func<TEntityType, TValueType>> propertyExpression, string columnName)
        where TEntityType : class
        where TValueType : struct
    {
        self.Property(propertyExpression).HasColumnName(columnName);

        return self;
    }
    /// <summary>
    /// Maps the property specified by the provided property expression to the column whose name matches the specified column name.
    /// </summary>
    /// <typeparam name="TEntityType">The type to be registered or configured.</typeparam>
    /// <typeparam name="TValueType">The value type of the property.</typeparam>
    /// <param name="self">The non-null type of entity that is having a property mapped to it.</param>
    /// <param name="propertyExpression">The expression that declares the property being mapped onto the entity type.</param>
    /// <param name="columnName">The name of the database column the property corresponds to.</param>
    /// <returns>The object this method extends, allowing for a more fluent mapping syntax.</returns>
    /// <remarks>
    /// This is a convenience method. This should be removed if the official EF framework is released with better property -> column
    /// mapping methods.
    /// </remarks>
    public static EntityTypeConfiguration<TEntityType>  MapByColumnName<TEntityType, TValueType>(
        this EntityTypeConfiguration<TEntityType> self, Expression<Func<TEntityType, TValueType?>> propertyExpression, string columnName)
        where TEntityType : class
        where TValueType : struct
    {
        self.Property(propertyExpression).HasColumnName(columnName);

        return self;
    }

    /// <summary>
    /// Maps the property specified by the provided property expression to the column whose name matches the specified column name.
    /// </summary>
    /// <typeparam name="TEntityType">The type to be registered or configured.</typeparam>
    /// <param name="self">The non-null type of entity that is having a property mapped to it.</param>
    /// <param name="propertyExpression">The expression that declares the property being mapped onto the entity type.</param>
    /// <param name="columnName">The name of the database column the property corresponds to.</param>
    /// <returns>The object this method extends, allowing for a more fluent mapping syntax.</returns>
    /// <remarks>
    /// This is a convenience method. This should be removed if the official EF framework is released with better property -> column
    /// mapping methods.
    /// </remarks>
    public static EntityTypeConfiguration<TEntityType> MapByColumnName<TEntityType>    (
        this EntityTypeConfiguration<TEntityType> self, Expression<Func<TEntityType, string>> propertyExpression, string columnName)
        where TEntityType : class
    {
        self.Property(propertyExpression).HasColumnName(columnName);

        return self;
    }

Much better.

Mapping our Existing Model

Now, as far as mapping the tables go, we need to provide the following:

1. The names of the columns the properties should get their values from
2. Designation as to what property represents the primary key
3. Any foreign key relationship

As far as foreign key relationships go, a look at the mapping methods tells us that we can establish a relationship with either table, be it the principal or dependent, however I find that it works best if we define the relationship on the class representing that table that actually has the foreign key assigned to it. This means we’ll be defining the relationship in the code responsible for the ResourceReservation class mappings.

The following code provides the mapping needed for the resource table:

    modelBuilder.Entity<Resource>()
      .MapByColumnName(r => r.Id, "resource_id")
      .MapByColumnName(r => r.Name, "resource_name")
      .HasKey(r => r.Id)
      .ToTable("resource")

The specification of the table name in ‘.ToTable(“resource”)’ probably wasn’t necessary; I’m assuming the Entity Framework would have been able to figure out the resource tables name itself, but I’m including it anyway (the corresponding real data type in the project I’m working on doesn’t match up with the table name).

The following code provides the mapping needed for the resource_reservation table:

    modelBuilder.Entity<ResourceReservation>()
      .MapByColumnName(rr => rr.Id, "res_id")
      .MapByColumnName(rr => rr.Start, "res_start_dt")
      .MapByColumnName(rr => rr.End, "res_end_dt")
      .HasKey(rr => rr.Id)
      .ToTable("resource_reservation");

    modelBuilder.Entity<ResourceReservation>()
      .HasRequired(rr => rr.ReservedResource)
      .WithMany(r => r.ResourceReservations)
      .Map(x => x.MapKey("resource_id"))

Note that I’m required to use ‘.Map(x => x.MapKey(“resource_id”))’ because the actual foreign key isn’t exposed on the ResourceReservation object. If it was, then I could use ‘.HasForeignKey(…)’ instead. It needs to be immediately available on the entity object we’re configuring. If I attempted to use ‘.HasForeignKey(rr => rr.ReservedResource.Id)’, I would have gotten an error.

One last thing…

When using EF with a preexisting database, you will need to disable the database initialization strategy, otherwise you will get strange errors during startup. You can do this like so:

    Database.SetInitializer<YourDbContext>(null);