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.

Comments

  • Anonymous
    June 15, 2008
    Is it possible to make the Category localized? I mean, if I added new column to the database called "CategoryNameFr" for instance, how would I tell dynamic data to choose this column when French is used as preffered language in the borwser and the "CategoryName" column for other cases?

  • Anonymous
    June 16, 2008
    Duken, one thing you can do is create a custom property on your entity class (e.g. LocalizedCategoryName), and have it return the right language column (e.g. based on the current UI culture). Please post any follow up to the Dynamic Data forum (http://forums.asp.net/1145.aspx), as this is not directly related to this post (which is about foreign keys).  Thanks!

  • Anonymous
    February 18, 2009
    Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in

  • Anonymous
    December 14, 2013
    Ok... but when we have the MetaForeignKeyColumn what is the correct way to obtain the table where entity reference came from?

  • Anonymous
    December 14, 2013
    sorry...I have found: ((MetaForeignKeyColumn)fk).ParentTable

  • Anonymous
    April 01, 2014
    Anyone suggest me a way to sort a column in a grid which is a foreign key. For eg. I have an employee entity with deptId as foreign key of Id of Department entity. In the grid the value of the Dept Id column is displaying department name because of ForeignKey.ascx Field template. I would like to sort the grid based on the department name and not on deptId?