Foreign Keys in the Conceptual and Object Models

If you are reading this, you have probably heard by now about the so called impedance mismatch between the relational world and the object world – and there are a number of concepts in the relational database that don’t translate easily to corresponding concepts supported by the object oriented paradigm. One of these factors that is particularly interesting (and often controversial) is the concept of Foreign Keys and whether or not they belong in your conceptual/object model.

FKs are used to represent relationships in the database – but with objects, the natural way to represent relationships is through real references between objects. So as an object relational mapping platform, should a product support one or the other, or both?

I think most will agree that there is tremendous value in supporting relationships in the model as first class references between objects.

What are some of the issues with supporting both foreign keys and references in the same model? We could take a real world example i.e. LINQ to SQL and its foreign key support to look at some of the benefits as well as the downsides to having foreign keys.

Foreign Key Support in LINQ to SQL

LINQ to SQL takes the simplistic approach of making foreign keys available to you as a scalar property in your entities. In essence, LINQ to SQL allows you to write code dealing with relationships like so:

Product chai = db.Products.Where(p => p.ProductName == "Chai").Single();
chai.CategoryID = 1;
db.SubmitChanges();

This is possible in LINQ to SQL mainly because foreign keys are somewhat central to the way relationships are implemented and supported.

This particular example achieves something quite powerful, however - here you have essentially changed the category that the product Chai belonged to without ever having to query and materialize the new category that you associated Chai with.

However, LINQ to SQL also allows you to do this:

Product chai = db.Products.Where(p => p.ProductName == "Chai").Single();
Category beverages = db.Categories.Where(c => c.CategoryName == "Beverages").Single();
chai.Category = beverages;

We have been considering for a while about whether or not we want to include support to allow you to expose foreign keys in the model. Let’s see what you gain / lose by having foreign keys in the model.

One of the fundamental issues is that the simple foreign key concept that works so well in the relational model isn’t sufficient to represent relationships in the conceptual model. In the Entity Framework, a relationship is a first class concept that must be mapped to any set of columns in the database – and the important thing here is that these columns don’t have to represent relationships on the database by the means of FKs and constraints. Another point to note is that in the Entity Framework, relationships are always comprised of two ends, and are bi-directional unlike foreign keys.

What does all of this do for the model?  This opens up possibilities, such as Referential Integrity constraints that are represented in the EDM even though they may not necessarily be present on the relational model in the store. Bi-directional nature of the relationships makes it possible for you to navigate in both directions along a relationship in a way that is generally not possible with a simple FK.

Foreign Keys are not without value, however. An interesting challenge for us is to figure out how to bring the best aspects of Foreign Keys into the Entity Framework without compromising the richness and flexibility that relationships bring to the table.  Let’s look at the upside and the downsides of plain relational foreign keys:

Benefits of Foreign Keys

1. Keeps it simple (for the simple cases)  and allows you to deal with relationship like you deal with them in the database
2. Technically, you can update relationships without having both ends loaded/materialized. This is however in reality not always interesting since you will likely load both ends but this feature is definitely useful.

Disadvantages of Foreign Keys in the Model

1. It is a part of the impedance mismatch problem.
2. It doesn’t allow the concepts that you would expect from relationships in objects (easily getting from one end to the other) for instance.
3. Having foreign keys as well as object references for relationship navigation presents the problem of two different artifacts representing relationships – this introduces complexity and now you have to make sure that you keep these two in sync.

So where do you stand? Do you like foreign keys or do you think they are evil? Would you like to see Foreign Keys exposed in the model, and if they are available in your object model, would it be sufficient if they were read-only?

Let us know!

Faisal Mohamood
Program Manager,
Entity Framework Team

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post.

Comments

• Anonymous
  October 27, 2008
  Personnally, I think it is a bad idea to have foreign keys in an object model because, even though they may appear as convenient for some uses, they're actually dangerous 'shortcuts'. They give a wrong feeling that it will be more lightweight to just set a FK value than setting an object reference ... because you will have to load the referenced object before setting the reference to it. The persistence framework should allow to return proxies that contain only the information about the primary key (and probably version/timestamp if any), and will not load the full object (until actually necessary), just to set a reference to it. More concretely, it must allow partial lazy-loading as well as its counterpart, eager-fetching. On a side note, why does the EF force us to always use bi-directional relations ? Some 'navigation properties' don't make sense, especially for 1-to-n relations (sometimes we don't care about the 'n' side, and navigation properties are 'polluting' the Domain Model).

• Anonymous
  October 27, 2008
  FYI steve, by default the EF generates two Navigation Properties for each association, typically this means one on each side. It is however perfectly feasible to remove one side. Which of course would result in a cleaner Domain Model in some situations. Alex

• Anonymous
  October 27, 2008
  I'd like to see foreign keys too. We currently use NHibernate and sometimes it's quite tricky to define the relations correctly (for legacy databases) but it's really worth the effort when you have to build a library that uses these entities and you simply don't have to care about some weird (read: multicolumn) relations. One feature that turned out to be very helpful is that a "broken" relation (i.e. the "1" part for a 1:n relation is missing) to define that the framework doesn't throw exceptions but simply sets the relation to null. Another useful feature is to allow access to the ID and the referenced object at the same time (where "ID" is a read-only property) for extremely weird situations (shudder).

• Anonymous
  October 27, 2008
  In our home-made persistent framework we used FKs to set some properties on newly created objects. When we move to EF, I had to make a wrapper around the EntityReference exposed in EF generated classes to "simplify" migration path. It works, but I had to make some workaround because changing the EntityKey on a referenced object don't set the IsLoaded property to false... To share my point of view : we are in a Data-Driven Model process and the database is the reference. So everyone has the knowledge of FKs. In this kind of architecture, the use of FKs is really a "performance optimization" operation.

• Anonymous
  October 28, 2008
  On the whole, I prefer LINQ to SQL's treatment of FK values, but I'd like to see their visibility made optional in EF in the same way pluralization is optional in LINQ to SQL and, presumably will be in EF. --rj

• Anonymous
  October 28, 2008
  The comment has been removed

• Anonymous
  October 29, 2008
  I have found the absence of FKs frustrating at times. I'm lucky in that most of the systems I have worked on have clearly defined single column identity keys. So we often pass these around on QueryStrings or put them in to ViewState etc. When it comes tiome to use tyhem we are forced to fetch an object from the database before tehycan be assigned. This is particularly annoying when it for a simple "lookup" table that may even have been previously hard coded into an ennumeration (I'm not saying that's necessarily a good idea!). I understand the arguments against them but can't help thinking there has to be a compromise somewhere. Thanks for listening, Jason

• Anonymous
  October 29, 2008
  I'm on the side of wanting direct access to the foreign keys.  Sometimes you pull a FK value out of a web form/querystring/viewstate/cache/etc and it would be nice to not have the extra overhead (both in performance and in code/productivity) involved in first doing a full entity lookup on the FK value in order to be able to do something with it. Thanks for soliciting input!

• Anonymous
  October 31, 2008
  For me FKs dirty the Data Model. I agree with Steve Degosserie completely and the existance of FKs in EF would drive me to simply continue to use AR and NH. I don't want to have to think about data concerns when working with objects. Why would I want to have to think about setting the CategoryID when what it is that i am trying to accomplish is setting the Category Entity.

• Anonymous
  October 31, 2008
  Yeah I'm sorry, the lack of foreign keys isn't going to cut it for me. I was trying to convert a LINQ2SQL project over to the Entity Framework, and there were a plethora of places where I either needed to retrieve the foreign key or change the foreign key to a value that was already known. Why should I sacrifice the scalability of my application and take the extra DB hit just to retrieve values I already know? I went back to LINQ2SQL.

• Anonymous
  October 31, 2008
  I really think foreign keys should stay out of the Enitites. The domain model shouldn't need them. I think many people ask for them because they want to perform actions where it would be good to know what the foreign key is. For example, lets say I want to delete all the audit records created on a certain day for a certain user. Well, the only way to do this currently in EF is to load all those records, delete them and then save. (Unless there is something I am missing.) So, I think alot of people are using an sproc to do stuff like this and they need the key to pass to the sproc. I think there are alot of these Batch processing cases that should be solved by EF without exposing the Db Keys in the Entities. Delete all the Customer entities with no orders older than 3 years for example. Generally in cases other than those you need some want to know the foreign key value. How do you know that the categoryID that you want is 1? You must have the category Entity at one time in order to determine this? If so, use that to set the relation. BOb

• Anonymous
  November 01, 2008
  While I don't think FKs should be forced on people, the absence of FKs is painful. Let's say you want to load a single entity from the database. This entity has numerous relationships with other entities. These relationships can be expressed either a) via an opaque foreign key value or b) via a reference to an object. If you choose b), then you must either load the associated entities and pack them into objects, or say "I don't care about the relationships, I just want THIS entity". In the last case, your object references will be null. The only problem with that is that this value, null, is often meaningful at the conceptual level. A null value can express "we didn't load this relationship from the database" but it can ALSO mean "we loaded everything from the database, but this particular entity didn't partake in this relationship". For example, a given entity Dog has a FK reference to an entity Owner. But not all Dogs have Owners; some are strays. So when you load a Dog object and it has no owner, you see a null value. And when you load a Dog object without loading its relationships, you see a null value--even though the Dog may, in fact, have an Owner. You just didn't load it. Anyway, to me, at the ORM level, a relationship is two things: an underlying but accessible foreign key, and reference to an object, possibly null, that can be lazy-fetched from the DB when it's access. You should have the ability to deal close to the metal with actual FK value, and you should have the ability to tell the framework "load all relationships", "load all relationships as needed" or "load no relationships".

• Anonymous
  November 03, 2008
  The comment has been removed

• Anonymous
  November 04, 2008
  While not on topic for the current thread, my apologies, but I have had not luck getting any response on whether binary primary keys are going to be supported in Entity Framework.  I do realize the support for GUIDs, but I have a legacy database that I wish to expose via Data Services, but I am not able to.

• Anonymous
  November 04, 2008
  Binary PKs are not supported in EF v1 (as you are apparently aware) and not likely to make EF v2.  It is a request we hear occassionally, but the backlog is long, and this one hasn't been getting as much love as some other things.

• Danny

• Anonymous
  November 11, 2008
  >So where do you stand? Do you like foreign keys or >do you think they are evil? Would you like to see >Foreign Keys exposed in the model, and if they are >available in your object model, would it be sufficient >if they were read-only? I'd like to get access to the FK's. I find it bothersome and ineffecient to have to populate the "other end" of an FK object. Why be forced to do a database lookup for each FK you save?  Plus, what if my table has 5-6 FK's in it? I'd be forced to do 5-6 table reads for each insert.... now THAT'S EVIL. Also, read only? That only solves half a problem for me. They must allow writes IMO.

• Anonymous
  November 13, 2008
  Hi Ryan, I'd like to clarify that you don't need to load something in order to have some other entity point to it. In the cases where you have an Order pointing to a Customer for instance, you can fabricate an EntityKey that essentially represents the foreign key of that customer and set it on the CustomerReference of the order. Sorry if the post was misleading to that effect. We realize the situation with FKs and this is why we wanted to solicit feedback. We are still thinking through this and will definitely keep the feedback in mind. Thanks. Faisal Mohamood

• Anonymous
  November 15, 2008
  It's worth mentioning that having the option to allow direct access to read/write FKs would definitely make migration from Linq2Sql much easier.  How Linq2Sql exposes FKs can sometimes be really nice (especially from a performance perspective), and it would be great for the EF to have feature parity here, especially since it appears to officially be the preferred platform over L2S now.  Having direct access to FKs would remove some of the extra weight that causes some customers to prefer Linq2Sql.

• Anonymous
  November 21, 2008
  It should be possible to expose the FK in the model, as read / write properties.   The loading that implies the transport of the FK objects, mainly with WCF, it is too much.  For example my table of employees has 300 columns, and I don't want to materialize and to transport that enormous object to show only the employee number in a GRID of nonattendances of the day.   My opinion doesn't mean that I don't understand your conceptual arguments, but I try to see it from a practical point of view and from the necessities of the developer.   Thank you per this opportunity of saying directly.

• Anonymous
  November 21, 2008
  I agree with the comment of Shawn.   Exactly we have 10% of a great application developed with L2S (and WCF), and we are evaluating the migration to EF.   This is the biggest problem that we find because we would have to make many changes in the code.

• Anonymous
  November 25, 2008
  I vote for the inclusion of FK's.  I've encountered difficulties implementing the PropertyReference pattern that Faisal mentions, and I fnd it counter-intuitive.  Optionally including FK's in the model would greatly simplify performing updates in disconnected scenarios.

• Anonymous
  December 01, 2008
  If the whole idea of the EF was to allow developers to work at a conceptual level, then FKs have no place.  FKs were a mechanism introduced to implement relationships, and in that respect the implementation was flawed.  Let's not propogate this flaw from the physical model back up to our new conceptual model.  Sure we have to support it somehow (there will be tons of badly designed databases we would want to use EF on top of), but the conceptual model is not the place.

• Anonymous
  December 19, 2008
  I would like to be able to choose to model relationships as associations or leave the foreign key column as a property on the entity. Then I could choose to model the relationship via a navigation property or via a query filtered on the foreign key column just like any other property of the entity. To avoid cluttering up the object model, I don't think there needs to be a navigation property and a foreign key property as well - the EntityReference exposes an EntityKey already. Given that I am striclty using the EDM designer only and that I have foreign key constraints in my schema, I would like to be able to delete an association in the designer and have it restore the scalar property and its mapping to the foreign key column. I also would like to be able to delete the reverse EntityCollection navigation property on many-to-one associations in the EDM Designer.

• Anonymous
  December 23, 2008
  Without foreing key, is a a paint to insert a record with 2 or mor foreing keys programaning

• Anonymous
  March 13, 2009
  As long as I can opt-out on the generation of foreing key properties (per project) the feature wouldn't bother me... Thx.

• Anonymous
  March 16, 2009
  Background A number of months ago we asked whether Foreign Keys (FKs) in Conceptual and Object models

• Anonymous
  March 17, 2009
  Definitely allow to add in foreign keys. If you do not add it, in my opinion this equals of the values of the foreign keys not having a meaning by themselves in the business context. Specifically when dealing with existing DBs foreign keys are often not just primary keys in identity-columns/GUIDs, they can be composite keys, they can be varchars that actually have a meaning by themselves. The framework should not force a concept of how a foreign key should look like and if it has any meaning by itself. And in my opinion the framework should not behave differently depending of the value type of the foreign key. So - definitely add the foreign key by default.

• Anonymous
  March 25, 2009
  I understand this feature is part of the impedance mismatch problem, but we really need this feature in some cases. E.g. in a web scenario a lot of times we have the ID value for the foreign key relationship but we don't want to fetch the object again, in these cases we would just want to set the foreign key property. Also in some N-Tier scenario's it can be very useful for us. Thanks, Jeroen

• Anonymous
  April 07, 2009
  Everybody agrees the foreign key is unnecessary so the question boils down to convenience. The key is available through the object reference, so the argument about it being convenient is weak. There are more downsides than you have listed. if you have two key values, which one does the data context believe? What happens when the related table uses a composite key? Finally, it's this a mapping issue? Presumably we can map as many or as few columns as we want, however we want, so why not default to generating necessary values only (read: NOT adding both a reference and a redundant foreign key). Finally, and this time I mean it, Hibernate and related technology have been using object reference only for almost a decade and nobody has complained. It's the right approach practically and theoretically

• Anonymous
  May 18, 2009
  I would favor FKs as read-only properties. This avoid the "2 independent references to the same entity" issue while allowing some performance improvement when it is only necessary to reference the FK and not the whole entity.  

• Anonymous
  May 19, 2009
  Please allow FKs as both read-only and Setting the Values. Will make Life much better.

• Anonymous
  May 30, 2009
  Better N-Tier Concurrency Management for the Entity Framework Posted in Technical at 11:03 am by Tony

• Anonymous
  June 03, 2009
  Background A number of months ago we asked whether Foreign Keys (FKs) in Conceptual and Object models

• Anonymous
  June 04, 2009
  KISS. Dont expose foreign keys in the Model because it is unnecessary and wrong. Dont take the kitchen sink approach please. There are other ways/alternatives  to mess in with foreign keys if you really need to.

• Anonymous
  June 10, 2009
  I think FKs should be exposed and read/write. Linq to SQL had a reasonable approach. By not including it, it's true that you're enforcing the object-relational approach, but you're taking away an option for developers. If it's that big of a deal, why not include the option to enable or disable FKs?

• Anonymous
  June 23, 2009
  I just want the ability to handle lookup tables.  I don't care if you put foreign keys in the model so long as I can map an object to a foreign key.   For example, if I have a Customers table and a CustomerTypes table, I want the conceptual Customers object to have a CustomerType string that can be linked to the Description on the CustomerTypes table.  This mapping should use the foreign key constraint to make sure that only valid CustomerType strings can be set.

• Anonymous
  December 15, 2010
  I agree that FK's would have to be in the EF for performance reasons.