If you’ve been playing with Entity Framework and used the wizard to create a model from an existing database, you’ll see that the resulting model  looks something like this which is a small portion of the EDM built from Northwind.

Compare the Orders Entity above to the Order table schema

and you’ll quickly notice that the foreign keys for CustomerID, EmployeeID and ShipVia are "missing".

We are used to using foreign keys as a means for defining relationships between tables and realizing them through the joins when we query. In our objects, we use the foreign keys as a means of finding our ways back to another object.

In the Entity Framework we have a different method of finding our way from one table to another – Associations & Navigations (which come as a team).

The model is more "holistic" than what we are used to.

The fact that there is a relationship between Orders & Customers is identified by an association (which in the model is represented by the lines between the entities). The Association is named "FK_Orders_Customers" and describes that in this relationship there will be 0 or 1 customers and many orders. (Though I can’t figure out how any order could be without a customer…)

So far this is only a part of the puzzle. Still nothing about CustomerID in there.

Then there are the Navigation properties. If you are working with a Customer entity, it uses it’s Orders navigation property to know that it has a relationship with orders and that relationship is defined by the association.

Okay so stop there a minute! Why have the Navigation property if we already have the Assocation?

The association is not part of the customer (nor part of the order). It floats outside of those. So when you are wokring with a customer, you need one of the properties to give you access to the orders, therefore a navigation property. The association is shared because the Orders entity has a property that allows you to navigate over to the customer when you are working with the objects.

When you create an Order object, while you don’t have Order.CustomerID, you do have Order.Customers, the Navigation property and that gets you to customers without having to say "where order.customerid=customer.customerid"

(From o In nw.Orders Where o.OrderID = 10281 Select o.Customers).First

This query will bring back  the customer for the specified order.

If you looked at the xml which defines the Associaitons and the Navigation proeprties in the conceptual model, you will see that there are still no references to customerid. So how does it work?

The fact that it is the customerid that is the foreignkey is defined in the Storage model. The Order entity in the storage model has a customerid and the FK_Orders_Customers association in the storage model has a reference to the CustomerID in it’s ReferentialContraint element.

Finally in the mappings, there is an AssociationSetMapping that very clearly defines that CustomerID is the means for relating Orders and Customers.

When the data is surfaced in the Conceptual model, all of the relationships have been sorted out and there is no longer (for most intents & purposes) a need to have the customerid in the order entity. We shouldn’t need to use that to navigate between orders and customers.

Some people beg to differ on that matter.  See this forum thread [http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1663304&SiteID=1] and also note that in response to a list of Entity Framwork suggestions by Roger Jennings , Mike Pizzo replies to suggestion #9.

9. Provide read-only access to foreign key values.

This is actually a feature I’m fighting to get into our final milestone for V1. Can you describe the scenarios where this is used? Do you need the ability to query on the foreign key value, or simply expose it on the domain object?

There’s one other thing that needs pointing out here. OrderDetails has an OrderID and a ProductID. So am I full of it? Well, that could be a different topic for debate, but in this case OrderID and ProductID are not only Foreign Keys but they are both Primary Keys in the Order Detail table as well. They become EntityKeys in the OrderDetails entity. Note the primary key icon for the two fields.