Understanding foreign keys and relationship columns in Dynamic Data

  • Article

Suppose you're dealing with a table that has a foreign key relationship with another table.  To take the canonical example, let's say we're looking at the Northwind Products table, which has a foreign key into the Categories table.  What this means is that each Product is a member of a Category (and conversely, a Category can be seen as 'containing' a number of Products).

What can make this situation a little confusing is that there are two related concepts that come into play, and can often be mixed up:

  1. The Foreign Key column: the Product class has a CategoryID property, which is an integer containing the ID of the Category that the Product is part of.  In some cases, there can be multiple Foreign Key columns used in a relationship (though this doesn't occur in Northwind).
  2. The 'Entity Reference' column: Product has a Category property, which is typed as a Category.  This is sometimes referred to as a 'Relationship' property.

#1 is a lower level concept that typically matches what's in the database, while #2 is a higher level concept which lets you work directly at the object level.

What makes this particularly confusing in Dynamic Data is that we made some pretty poor decisions when it came to naming some of our classes.  Specifically, we are using the type MetaForeignKeyColumn to refer to the 'Entity Reference' column (#2), even though the name makes it sound like it's referring to #1.  Bad naming (sorry!), but we have to live with it now! :)

So if we're using the MetaForeignKeyColumn class for Entity References, what class are we using for real Foreign Key columns?  We don't have a special class from them, and we simply use the base MetaColumn class (which MetaForeignKeyColumn extends).  But you can recognize them  because their IsForeignKeyComponent property returns true.

Note that when you use Dynamic Data's scaffolding feature, by default it only shows the Entity Reference column and not the FK column.  This is because in most cases showing the CategoryID is completely uninteresting, and what people want to see is something more descriptive about the Category (e.g. its name), which is what the Entity Reference columns gives you.

Another thing worth noting is that there is a difference between Linq To SQL and Entity Framework when it comes to foreign  keys.  With Linq To SQL, your entity class has a property for the FK (e.g. CategoryID), while with Entity Framework you won't have that property at all.  However, in both cases you will get the Entity Reference (aka the MetaForeignKeyColumn in Dynamic Data), which is the one you usually care about.