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 (10)

  1. Martin Laufer says:


    I suppose a different mapping.

    As the three entities A, B, and C are assingment compatible, they schould share a common base class (let’s say abstractBase) or implement a common interface (let’s say IBase) Thus may lead to TPH for the entities A, B, and C (but not required to!).

    The relationship end in Foo is then of type abstactBase or IBase. So we need only one member (ID) and we need to persist type information (discriminator). Of course we have to add a constraint between the discrinminator and the actual type of the referenced entity.

    – Martin

  2. simmdan says:

    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.

  3. Martin Laufer says:


    as the text says, that a Foo entity has a relationship with EITHER entity A, B or C, they are assignment compatible. So they will share a base type.

    That particular type may contain nothing more than an entity key. We can (surely) always construct such a base type (let’s take System.object).

    But yes, if the problem domain states, that we in fact have 3 relationships between different entities (which are additionally constrained), then You will be right.

    Your suggestion #3 leads me to some serious questions:

    How the term "Entity" is defined (in EF)? How does it differ from "Complex Type"? How differs the Tterm "Relationship" from "Complex Type"?

    I understood it that way (please correct me, if I miss something):

    "Complex Type"

    A complex type is the base for entities and relationships. Its a relation created of simple types (like int, string, date …). (Like in XSD)

    Instances neither have an Identity by its own nor an independent lifetime. When mapped to a relational database a complex type usually needs an additional surrogate key to ensure uniqueness for the primary key(but not required, there may be timestamps…)


    An entity is a complex type, whose instances have an Identiy by its own (ensured as entity key) and an independent lifetime. When mapped to a relational database, the primary key of the relation is the entity key (which is always an attribute from the problem domain, not a modeling artifact). So no surrogate keys are needed.


    A relationship is a complex type, whose instances have an Identity by its own (created by the combiation of the referenced entity keys) but do NOT have an independent lifetime. Their lifetime is merely bounded by the lifetime of the refrenced entities. When mapped to a relational database the primary key of the relation is formed by combination of the foreign keys of the referenced entities.

    Taking that into account, Your Option #3 is somewhat problematic to me. Does the (future) containment relationship turn the child entities into complex types (by restricting their lifetime)?

    btw: where to post feature requests for EF?

    – Martin

  4. simmdan says:

    Your definitions of Complex Type, Entity and Relationship are close, but not quite the way we think about them, and I think that leads to some of the confusion.  A Relationship is not really a Complex Type in the sense that you imagine.  Also, a complex type is not just defined by the fact that it’s lifetime is restricted to that of the "parent" entity.  A key aspect of complex types is that they fundamentally have no identity–not just that you would have to use a surrogate key to map them but that they are more like value types.

    Creating a "containment" relationship (which by the way is just something we’re talking about–by no means something we’ve decided on, and even the name is not decided since the industry terminology in this area is not standardized and often confusing) is really just a matter of attaching some behavior to the relationship.  With the EF today, it’s already possible to specify cascade delete behavior on a relationship such that if you delete one entity, the entity on the other side will also be deleted.  Further, if you specify a referential integrity constraint on a relationship in the EF today, that means that the identity of the dependent (or child) entity is slaved in part to the identity of the primary (or parent) entity–because the key of the dependent entity contains (at least in part) a foreign key to the primary entity.  So if you remove the primary entity, the dependent entity must also be removed.  In fact, if you just undo the relationship between the two entities, the dependentiy entity must be removed because it’s identity was defined in part by that relationship.

    So the difference between this kind of relationship and a complex type is that a complex type is not at all addressable separate from the parent.  A dependent entity (whether through RI constraints or a containment relationship or whatever) is one which can be addressed (and updated) individually because it has its own identity–just its identity and lifetime are related to something else.

    The big difference between the proposed containment relationships and the existing RI constraints is that dependent entities would be further constrained so that they could have exactly one primary entity even if multiple relationship possibilities were defined.  This allows modelling things like a file which could be related to a filesystem folder or to an email message as an attachment but never both at the same time.  The two relationships are different, but only one can be set at a time.  Further, in either case if the parent is removed the child should also be removed.

    Hmmm…  Interestingly, these containment relationships can be a bit challenging to think about it the context of the scenario above, because one might think about Foo as the parent and A, B or C as children in which case containment relationships wouldn’t help since they say that the child can have only one parent, but they don’t say anything about a parent having only one child.  So you would have to model the relationship so that Foo is a child of either A, B or C–which might work for your model or it might not since the relationship would also determine lifetime and it’s not clear which lifetime is the one that should determine things.

    – Danny

  5. simmdan says:

    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:

    – Danny

  6. macsgold says:

    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?!


    -Mat Hobbs

  7. 247Blogging says:

    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

  8. zhirani says:

    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?

  9. simmdan says:


    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