Often in the context of using the Entity Framework there is a one-to-one relationship between entities and database tables. While, from a functional perspective, that’s fine, entities are not restricted to representing only database tables. We can treat other things from the database as entities, such as views, as well. We can even establish cardinality between views and other entities, yielding results similar to using database tables that exhibit a type of cardinality with each other. The relationship between views and entities if established will not be entirely orthodox of course, mainly due to the inability for a view to have either a primary or foreign key. Consequently, using views with Entity Framework 4.1 (Code First) requires some unorthodox mapping.

Let’s see how we can define a model that consists of a table with references to data from a view.

I. The Domain Model…more or less…

First, to set the mood, the reason this topic entered my thoughts stems from the use of the Entity Framework with a preexisting, and very legacy, database I’ve had to tango with in various projects. However, the proposed model and implementation example detailed here is entirely fictional, at least assuming that when we say “fictional”, that we do so under the assumption that “x <= 99% non-fictional” always translates to “fictional”.

First, assume we have been given a database to work with, and we have no input on how the actual database schema should be structured. Because we are all intelligent people, we know then that debating on whether the database demonstrates good design or not is pointless.

Assume we’re dealing with an application that has a data model that, among other things, consists cities described by City objects. Let’s write out our domain class for this:

public class City
{
  public int Id { get; set; }
  public int Name { get; set; }
  public int Population { get; set; }
  public string Description { get; set; }
}

Nothing spectacular here.

Moving on, let’s assume City objects are stored in a table named CITIES.

OK. Now, let’s assume that Cities can be given a Rating. A Rating can be applied to a City, as well as pretty much every other data type in the application.

Finally, let’s assume that, for some reason, the original “designers” decided that a Rating consisted of a uniquely identifying literal, a display name, and a URL pointing to an icon representing that Rating. Let’s whip up a class for this:

    public class Rating
    {
      public string Code { get; set; }
      public string DisplayName { get; set; }
      public string ImageUrl { get; set; }
    }

Taking an additional step towards the general region of insanity, let’s assume that the data that defines a Rating itself is not stored in a dedicated table, but in a table containing other types of static data, categorized appropriately, somehow and someway. To get any use out of it, a view is used to aggregate the defining data from these indiscriminate locations into a more logical and self-contained block. Let’s assume the name of the view is RATINGS.

Not quite done yet: to complete the journey to the Land of No Reason, the decision was made that ratings would be associated to cities through the assignment of the Rating’s “Code” value to a dedicated column in CITIES table. How can we describe this strange relationship using Entity Framework? Well, before that, we need to add a reference from entity to the other in one or both of the classes.

II. Referencing the Other

Let’s first update our City class.

    public class City
    {
      public int Id { get; set; }
      public int Name { get; set; }
      public int Population { get; set; }
      public string Description { get; set; }
      public virtual Rating { get; set; }
    }

Notice that the City class now has a Rating property. What it does not have, is a property meant to contain only the Rating’s code. This is contrary to the actual legacy table, which simply has a tiny varchar column probably named something obscene, such as “cityratingcd”, dedicated to holding just the Rating’s code.

Hell, while we’re having so much fun here, let’s add a little bit to the Rating class:

    public class Rating
    {
      public string Code { get; set; }
      public string DisplayName { get; set; }
      public string ImageUrl { get; set; }
      public virtual ICollection<City> Cities { get; private set; }

      public Rating()
      {
        Cities = new ObservableCollection<City>();
      }
    }

You’ll see that we added a new property named Cities above. Hmm? Cities? Yup, and it will return all cities that currently have that particular Rating assigned to them. Now, I did say previously that there were types other than City in the system, and that Ratings could be applied to them as well; however, I don’t have anything else defined in this example, so just substitute City for a common interface in your heads if need be. Also, this is certainly not needed if you don’t want it. The association can be purely one-way, obviously. I’m using this purely for example purposes.

III. Creating the Data Model

How we actually set up some sort of relationship between these two all boils down to what we do during the model creation phase. First, let’s see how we end up defining our City type during model creation (note: I’m going to be using some extension methods I covered in some previous articles, namely for column mapping). Let’s also assume all of the corresponding column names in the legacy database are named very “legacy-like”.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      // Map City class to legacy CITIES table.
      modelBuilder.Entity<City>()
        .MapByColumnName(c => c.Id, "city_id")
        .MapByColumnName(c => c.Name, "city_name")
        .MapByColumnName(c => c.Population, "city_population")
        .MapByColumnName(c => c.Description, "city_description")
        .HasKey(c => c.Id)
        .ToTable("CITIES");
    }

The above mapping makes no mention of the “citystatuscd” column which holds the associated Rating’s code for the City here. I can define a relationship from either the City or Rating perspective during model creation. I can define it from both perspectives, but only one is needed. We’ll define it in this case from the Rating’s perspective. Obviously, if you elect to not have the collection of cities in the Rating class, you would be required to form the association from the City’s perspective instead.

Now, let’s map the Rating type itself. If you’ve been using EF 4.1 for even a little bit, you’ll probably know that an Entity is invalid if it doesn’t have an identifiable primary key. Because a VIEW cannot have a primary key, it would seem like we are in a bad situation. That would be true, if we were obligated to always be truthful to the Entity Framework. Thankfully, we’re not, so we can go ahead and tell EF that a specific property is the key for the type, even though it isn’t. The actual effect of designating one property as the key vs. another is outside the scope of this article, but is probably an interesting thing to look at.

Obviously, in our case, we’ll designate this strange “Code” property as the key.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      // Map Rating class to legacy RATINGS view.
      modelBuilder.Entity<Rating>()
        .MapByColumnName(r => r.Code, "rating_code")
        .MapByColumnName(r => r.DisplayName, "rating_display_name")
        .MapByColumnName(r => r.ImageUrl, "rating_image_url")
        .HasKey(r => r.Code)
        .ToTable("RATINGS");

      // Map City class to legacy CITIES table.
      modelBuilder.Entity<City>()
        .MapByColumnName(c => c.Id, "city_id")
        .MapByColumnName(c => c.Name, "city_name")
        .MapByColumnName(c => c.Population, "city_population")
        .MapByColumnName(c => c.Description, "city_description")
        .HasKey(c => c.Id)
        .ToTable("CITIES");
    }

The above Rating mapping will successful allow us to populate Rating instances with data reflected in RATING views. But wait, we forgot to declare the association between Rating and City in the above snippet…….

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      // Map Rating class to legacy RATINGS view.
      modelBuilder.Entity<Rating>()
        .MapByColumnName(r => r.Code, "rating_code")
        .MapByColumnName(r => r.DisplayName, "rating_display_name")
        .MapByColumnName(r => r.ImageUrl, "rating_image_url")
        .HasKey(r => r.Code)
        .ToTable("RATINGS");

      modelBuilder.Entity<Rating>()
        .HasMany(r => r.Cities)
        .WithRequired(c => c.Rating)
        .Map(x => x.MapKey("city_status_cd").ToTable("CITIES"));

      // Map City class to legacy CITIES table.
      modelBuilder.Entity<City>()
        .MapByColumnName(c => c.Id, "city_id")
        .MapByColumnName(c => c.Name, "city_name")
        .MapByColumnName(c => c.Population, "city_population")
        .MapByColumnName(c => c.Description, "city_description")
        .HasKey(c => c.Id)
        .ToTable("CITIES");
    }

This should give you data mapping functionality that will link City instances with their correct Rating and Rating instances with all the Cities that have that Rating assigned to them.

Matt Weber

I'm the founder of Bad Echo LLC, which offers consulting services to clients who need an expert in C#, WPF, Outlook, and other advanced .NET related areas. I enjoy well-designed code, independent thought, and the application of rationality in general. You can reach me at matt@badecho.com.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
   
© 2012-2013 Matt Weber. All Rights Reserved. Terms of Use.