An Interesting Relationship Mapping Scenario

This week on the ADO.Net pre-release forum, someone brought up an interesting relationship mapping scenario in this thread.  Essentially the scenario is this:  They have a table which represents an entity, call it Foo, and along with the other columns, the Foo table has a column which is an foreign key ID and another column which denotes a type.  Foo entities have relationships with three other entities, call them A, B & C, but they can only have one relationship at any one time.  The type indicates which relationship is in use, and the ID is then treated as a foreign key to which ever table the type indicates.

On one level, this makes perfect sense even if it's not the same kind of pattern we usually think about, but on another level it's not hard to see why the EF (and other ORMs) do not support this out of the box.  The problem is that there's no good way to describe in the conceptual model the fact that an instance of Foo can only have one of the three relationships at any particular time.  The object model would actually end up with references to all three types, so it would be all too easy to set more than one of those references to a non-null value and then not be able to save changes because there's only one ID column.

Nevertheless, here's a customer whose database funcitons this way today with hand-written code, and it seems like we ought to be able to do something with the EF.  So after some thought, I came up with a few alternatives:

Option #1: Use inheritance and relationships between subtypes.
It's not hard to figure that this solution won't work in many situations, but if it will work for a particular situaiton, then it's the most natural option.  Basically the idea is to use the type field to create a TPH inheritance hierarchy of FOO types.  You could call them FooA, FooB & FooC, where each of these subtypes has a relationship to one of the other types.  The nice thing about it is that the conceptual model now understands that only one relationship can be in place at a time, and the object model matches as well since each type only has one reference to the other type which it can be related to.  There are two major problems, though:  First off, Foo might already have or be part of another inheritance hierarchy in which case inheritance isn't really an option.  Secondly, this mechanism assumes that any one Foo instance has the same relationship throughout its life.  It would not be possible to take a FooA and decide later to relate it to B instead, because that would require destroying the instance of FooA and creating a new instance of FooB.

Option #2: Use a view to transform the storage table plus sprocs for updates.
A more creative approach would be to create a view in the database which does not expose the ID & type columns directly the way the underlying tables does but instead exposes 3 different ID columns--call them IdA, IdB, & IdC--each of which has the ID value only if the type indicates a realtionship to the corresponding entity and otherwise is null.  Once you do that, Foo can have three null-able relationships where the association sets are mapped only if the value is non-null.  That way, only one of the relationships would be active at a time.  The Foo object model would have 3 references but two of them would be null at any one time.  No inheritance hierarchy would be needed, and instances could change which other entity they are related to just by setting the appropriate reference to a non-null value and the other ones to nulls.  The downside is that you have to write the view and, more importantly, that all updates would have to go through stored procs since such a view would not be directly updateable.  Essentially this mechanism would fool the entity framework into thinking that the database looks differently than it actually does, and it would depend on the sprocs to enforce validation of the relationships when updating rather than doing the enforcement itself.

Option #3 (in a future EF release): Model the relationships as containment.
One last possibility which I'll mention is that we have talked about adding to the EF in a future release the idea of a containment relationship.  The basic idea of this construct is that "child" entities maybe related to one or more "parent" entities but only one parent at a time.  This would then allow the conceptual model to more directly represent the idea in the database, and we would then be able to create a declarative mapping for the scenario.  I will point out, though, that this definitely won't make v1, and there are still a number of details to work out in the design.

While probably none of these is exactly what the original requester had in mind, I was pleased to discover that the EF is flexible enough to provide some decent alternatives.

- Danny

Comments

• Anonymous
  November 17, 2007
  PingBack from http://msdnrss.thecoderblogs.com/2007/11/18/an-interesting-relationship-mapping-scenario/

• Anonymous
  November 18, 2007
  The comment has been removed

• Anonymous
  November 19, 2007
  Well, the interesting part is if they don't share a common base class.  If they do share one, and especially if they can all be put in a single entityset, then you can just use a single relationship and dispense with the type column altogether, because existing relationships handle that case.  If they don't fall in the same entityset, then existing relationships can't handle coallescing things into a single relationship because the existing Entity Framework relationship management/fixup code won't deal with the case.  So you could do something like what you suggest, but you would have to do it all in the partial class (if you were doing code gen) and manage everything about the relationship yourself. That does generate another interesting idea, though.  If you could come up with a common type, then you could define three relationships in the model but not declare navigation props for them.  Then you could write a couple of properties in the partial class--one which indicates the type and one which maps to the appropriate underlying relationship depending on the type, but you would still be in case 2 above where you have to ues a view and sprocs.

• Anonymous
  November 19, 2007
  The comment has been removed

• Anonymous
  November 20, 2007
  The comment has been removed

• Anonymous
  November 20, 2007
  By the way, I forgot to respond to the question about where to post feature requests for the EF...  Probably the best place for that kind of thing is the ADO.Net pre-release forum which you can find at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1

• Danny

• Anonymous
  November 26, 2007
  I think option #2 is the best and simplest approach.  There will always be 'interesting' relations between objects and they will be modeled in interesting ways in the RDBMS and in the application. The rule that the entity can only be related to one of the three parents is a business rule and I don't think EF should attempt to address it (just as the RDBMS does not attempt to address it). If you try to go down path #3 you will find it a path with no end and grow ever more complicated mapping mechanisms. What if the business rule changes so that the entity must map to any two (mo more, no less) parents but not to only one or three?! Cheers, -Mat Hobbs

• Anonymous
  May 19, 2008
  The comment has been removed

• Anonymous
  April 17, 2009
  Hi Danny, Did option 3 made it to the next release of EF. If so can u hightlight how does that work for next version?

• Anonymous
  April 17, 2009
  @zhirani, Option 3 did not make it into EF4 (that is the version of EF shipping with .net 4/vs2010).  There are so many other features in this upcoming release that we just didn't get to the point of adding in containment.

• Danny