Re-mapping entity framework associations

Posted: 25th January 2010 in Entity Framework

Problem : You have two database tables which map on a one to one relationship with the same primary key. In turn, both of these table have foreign key relationships to other, different, tables. If you had control over the database, you would probably move all the columns into the same table. However, in this scenario, you don’t have any control over the database, and if you changed it, there are other existing application which use it.

Tow Entities sharing a primary key

Solution : After creating an entity model from the existing database you need to make one entity out of two tables and remap the existing association to the new entity columns.

As can be seen in the NetworkLocation and the Location entities both share the common primary key LocationCode. Our first step is to move the existing columns in NetworkLocation to the Location entity, this can be done using the following steps:

  1. Copy and paste all the columns, except LocationCode from NetworkLocation to Location
  2. Delete NetworkLocation from the diagram
  3. Right click on the Location entity and select table mapping
  4. in the mapping details window, scroll to the bottom where it says “Add a Table or View”
  5. Select the drop down and choose the table which mapped to the entity you have just deleted
  6. Remap the entity to properties to the underlying table, as can be seen below

remap the entitiy to old table

Now, as can be seen below, the entity properties have been moved, but the reference to the NMC entity has been lost. To allow us to remap this, we need to look at the underlying XML.

Properties of the entity have been moved

To do this, in solution explorer, right click on the edmx file and choose “open with”. In the displayed dialog box, choose “XML Editor” and you should now see the underlying XML.

One thing to remember is that when you delete the entity from the diagram, all you have done is delete it from the conceptual model. The relationship in storage model still exists.

So, bearing that in mind, we need to navigate to the conceptual model part of the the XML. Now, we need to add XML fragments, in four places as described below:

  1. Firstly, copy the existing AssociationSet from the Storage Model to the Conceptual Model in the entity container section and remove the word “Store” from the association attribute value. Then rename the EntitySet to the declared in the EntitySet Element (highlighted in yellow in the diagram below)
    Adding AssociationSet to XML
  2. Next, scroll to the bottom of the conceptual model and add in the association, just before the schema tag.
    re-adding association
    Note: the name in the type attribute should be the same as the name of your entity.
  3. Now, you have these two in place you should be able to open the model and see the two entities reconnected. Give this a try before going any further.

  4. Once you have, this relationship in place, you need to add the navigations back in. These need to be added in two places: to the location entity and to the NMC entity, as seen below
    Remapping naviation properties

    Okay, that’s us finished with the XML and we can do the rest through the GUI

  5. Select either of the navigation properties and remap the initial table columns to the entity by adding the table and choosing the columns from the drop down.
    re-map existing table to new associations

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s