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.