Entity Data Model Associations: Where’s my Foreign Key?

 

    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.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

26 thoughts on “Entity Data Model Associations: Where’s my Foreign Key?

  1. I have a problem. I have 2 entities connected with an association of type string. Problem is when the key field contains special characters such as ‘.’, ‘@’, relation fails.I am doing something wrong ?

  2. I’ve avoided string key fields except when working with the ol’ Northwind database. I don’t know if this is an EF issue or ADO.NET Data Services. My guess is that EF just doesn’t support it. But you should get a confirmation and explanation. I would ask about this in the EF forums [http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1]. Start a thread titled something like "Associations using non-alphanumeric characters" or soemthing like that and see what response you get. I’ll keep my eye open for the question so I, too, can learn the answer.

  3. how can I filter my select statement with a field in my table which that field is a reference key?

    eg.

    db.Products.Where(p => p.CompanyReferenceKey == "some"); //error

    thanks

  4. @Majid

    An EntityKey is not a scalar value but comprised of a number of values, e.g.

    new EntityKey("db.Companies","CompanyID",123)

    In a query you can navigate directly to the related entity’s id

    db.Products

    .Where(p=>p.Company.CompanyID==123);

    hth

    julie

  5. Ok so if I no longer have CustomerID exposed in my Orders, and I create a new order object in code…how the heck do I set the CustomerID value to be saved into the database? Without being able to do this I have orders just floating around in the database not attached to anything, which is definitely of no use to me.

  6. @Chuck

    You ahve a few options in EFv1.

    1) if you have the customer entity in memory then you can say order.customer=myCustinMemory

    2)if you know the ID you can set it this way:

    order.customerReference.EntityKey=newEntityKey("myEntities.Customers","customerid",value)

    3) last is to create a stub entity as in Alex James Tip #26: http://bit.ly/18Fabo

    hth

    julie

  7. I have the same problem in adding a new record to Order table without able to assign a value to the foreign key (customer id). Can you show the complete code? here i attach a sample:

    db.AddToOrder(new eBookSystemModel1.Order()

    {

    …..

    //assigning entities of order

    }

    where should i put the customer id?

  8. yvaine,

    The code you seek is in the comment just above your comment. Option #2 is the most direct way (using the ContactReference EntityKey).

    hth

    julie

  9. thank you for your prompt reply. I did try option 2. The customerReference is recognized but not customerReference.entitykey. Did i miss out something?

  10. Let me clarify my problem. I have a Payment table with qty, subtotal and bookID as foreign key. Below is my sample code to insert a new payment record. How should i insert the bookID?

    db.AddToOrder(new eBookSystemModel1.Order()

    {

    Qty = 1,

    SubTotal = 12.50

    }

    );

    db.SaveChanges();

  11. Hi Yvaine

    Following the customerid example, and ssuming your bookid is a variable called myBookID… You can’t do this in the object initializer which means you will need a variable for the instance of the new order.

    Order newOrder= new eBookSystemModel1.Order()

    {

    Qty = 1,

    SubTotal = 12.50

    }

    newOrder.BookReference.EntityKey=

    new EntityKey("eBookSystemEntities.Books","BookID",_mybookid);

    db.AddToOrder(newOrder);

    I’m making a few assumptions.

    That you have a navigation property in order called Book and therefore another automatically created property

    called BookReference. I’m assuming that the EntityContainer name is "eBookSystemENtities" and that the Book entitySet name is Books. You’ll have to adjust accordingly.

    hth

    julie

  12. Julie,

    I have a two entities, Category and PageContent. Category contains a reference to PageContent that can be NULL. Because PageContent is relatively expensive to load, is there any way to check and see if the reference is NULL without calling Load()? The underlying Category table in the DB has a nullable PageContentID foreign key.

    Thanks

  13. wow, this is lame. I can’t believe you don’t have access to FK in entities without jumping through hoops.

    I’ll go back to linq-to-sql…

  14. @Ryan

    The PageCOntent reference will never be null, but PageContent.EntityKey will be null if there was no related PageContent when Category was pulled in from the db.

    does that help?

    julie

  15. Is there an easy way to get a corresponding table name for the given entity?

    Notice that the entity type could be different from the underlying table like this

    ‘employee’ – table name

    ‘super_employee’ – corresponding entity name.

    Thanks!

  16. Thanks for clearing this up, however my problem is simple I am plugging RIA services into the EntityModel and need to apply data annotations to the foriegn keys and cant, has anybody else come accross this?

  17. thank u very much for the answer.if an entity have only one primary key n 3 to4 foreign keys then is it weak entity or strong entity

  18. The FK is missed also if the primary table contains an Index on Primary key field (Mistake on creating the index). I lost 3 hours on it!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.