Entity Framework – Removing Foreign Keys from the Entity Model

March 18, 2011 • Damien White

DatabaseDesignEntity Framework 4.0 has brought some great new features to Entity Framework (although I am SO looking forward to EF 4.1.  Hooray for a code-only option).  One of the new features of EF 4 is the ability to include foreign key columns in your model.  This option will include the foreign key as a property within your entity in addition to the Navigation Property.  The advantage to doing this is that you can now perform linkage between entities with only needing to fill the foreign key property instead of filling the navigation property.  It works pretty well, unless you want to have something like a zero or 1 association (0..1).  Attempting to change the multiplicity in the designer for the association yields the following error:

Error 113: Multiplicity is not valid in Role in relationship X. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be *.

Blah.  The solution I found to fix it was to remove the foreign key association on the 0..1 entity.  Now how do we go about doing this?

This post assumes that you added entities with the foreign key columns included in your model and now you need to make changes to your associations.

The Easy Way: Remove and Update From Database

Let’s start with the easy way, which may not work in all situations.  The idea here is to remove the entity that should be the 0..1 entity and UNCHECK the “Include foreign key columns in the model” option when adding the table back.  Here’s the steps:

  1. Remove the entity that shouldn’t have the foreign keys defined on it (your 0..1 entity).
  2. Right-click and choose “Update Model from Database.”
  3. On the Add tab, select the table for the entity that you removed.  UNCHECK the “Include foreign key columns in the model” option and click finish.
  4. Back in your model, find the association that you want to change from * to 0..1 in your model.  Change the End2 Multiplicity in the Properties window to 0..1 (Zero or One of X).
  5. Change the navigation property on the parent entity to be singular (it’s most likely plural when you regenerated the table).

That should be it.  Pretty easy, but if you’ve done a lot of customization to your entities, then this may not be the best option for you.  You can always dig into the XML of the EDMX by right-clicking the EDMX file and choosing “Open with” and then picking the XML (Text) Editor.

The Hard Way: EDMX XML Fun

If you don’t want to delete your entity and re-add it from the database, there is another, more complicated option.  You can always dig into the EDMX’s XML and make the changes yourself.  Word of warning, be sure this is the only change you are making when you do this (no other pending changes).  Make sure everything is checked in before modifying your model.  If something goes wrong here, the best option is to undo your pending changes and start over.  You can really hose your model, so make sure you have a safety net.

  1. In the conceptual model, remove the FK property, typically something like ChildId or whatever.
  2. Still in the conceptual model, change the navigation property to be singular.
  3. Once again in the conceptual model, remove the ReferentialConstraint for you Association.  Say you Association Name is something like FK_Child_Parent, just dump the ReferentialConstraint in there.  Note you can also set the multiplicity here if you need to; you would set the correct multiplicity on the end point.  Your options are *, 1, or 0..1.
  4. Now move down to the C-S Mapping Content (the mappings), and remove the ScalarProperty that corresponds to the foreign key you are removing.
  5. Finally, since you removed the ReferentialConstraint, you need to add an AssociationSetMapping instead.  You can just append this to the end of the EntityContainerMapping section of the XML.  Below, you can find a snippet of the AssociationSetMapping. **Be sure to replace the attributes within the **AssociationSetMapping that are all in caps.

The AssociationSetMapping Code

Let’s start with the AssociationSetMapping XML (from Step 5 above).  Don’t worry, I’ll explain it afterwards.  Again, make sure you have your model safely checked in before you start modifying the XML, that way you can always rollback in case things go wrong.

...

<edmx:Mappings>
  <Mapping Space="C-S" ...>
    <EntityContainerMapping ...>
      ...
      <AssociationSetMapping Name="FK_CHILD_PARENT" TypeName="MY_MODEL.FK_CHILD_PARENT" StoreEntitySet="CHILD_ENTITTY_TABLE">
        <EndProperty Name="PARENT_ENTITY">
          <ScalarProperty Name="THE_PARENT_KEY_PROPERTY_ENTITY" ColumnName="THE_CHILD_FOREIGN_KEY_PROPERTY_DB" />
        </EndProperty>
        <EndProperty Name="CHILD_ENTITY">
          <ScalarProperty Name="THE_CHILD_KEY_PROPERTY_ENTITY" ColumnName="THE_CHILD_PRIMARY_KEY_DB" />
        </EndProperty>
        <Condition ColumnName="THE_CHILD_FOREIGN_KEY_PROPERTY_DB" IsNull="false" />
      </AssociationSetMapping>
    </EntityContainerMapping>
  </Mapping>
</edmx:Mappings>

...

As promised, this needs a bit of explaining.  The best explanation of the AssociationSetMapping comes from the MSDN documentation.  Next, the EndProperty is fairly straight-forward, it is just the entity name of the endpoint.  You’ll have two, one for the parent entity and one for the child entity.  More information again, available on MSDN.  Inside the EndProperty, you need to have a ScalarProperty, which maps the conceptual model property (Name) to the name of the table column (ColumnName).  Lastly, the Condition is an optional element that is a condition for the mapping.

Posted in entity framework, sql and tagged with Entity Framework, Microsoft SQL

Damien White

I am a software architect with over 16 years of experience. I simply love coding! I have a driving passion for computers and software development, and a thirst for knowledge that just cannot be quenched. I'm happy to share what I know in my quest to learn as much as possible. I focus most of my time on web development using Ruby on Rails, Ember.js, and ASP.NET MVC.

comments powered by Disqus