Freigeben über

Entity Data Model: working with foreign key when you don’t have it

  • Artikel

It’s been debatable whether to include FK (foreign key) columns in the conceptual model. That’s why in Visual Studio 2010, a checkbox is added to Entity Data Model Wizard, so that the decision is left to the developers. While I was playing with it yesterday, I found FK particularly useful in the following scenario.

The application I was trying to build is really simple: change an order’s related customer from A to B.
clip_image001

If FK (CustomerID) is in my Order entity, I could just do a lookup binding to this FK column, and call dataContext.SaveChanges().The drag-n-drop way of doing lookup binding to a ComboBox is to first drag the FK property (in this example, CustomerID in Order entity) with a ComboBox control, and drop it onto the form. Then drag the related lookup table (Customer entity) and drop it onto the previously created ComboBox control. The SelectedValue, SelectedValuePath, ItemsSource, and DisplayMemeberPath properties will be set for you.

Things become a little bit tricky if I don’t have the FK in the entity.

Data bind Orders to a DataGrid

This step is fairly easy. Just drag Orders node onto the WPF Designer.
clip_image002

Create lookup binding to a ComboBox

Because I don’t have the FK column in the model, there’s no CustomerID property in Order entity. To create the lookup binding, I need to expand the navigation property Customer in Orders, and bind the CustomerID to a combobox.
clip_image003

After I drag-n-drop this node onto WPF Designer, I will have created a complex binding. To make it a lookup binding, I will link the newly created combobox to the lookup table. The way to do it is to drag the Customers node (marked with 2 on the picture above), and drop it onto the combobox. Please notice that while doing this, your cursor will change from a little + to an arrow, to indicate that we will not generate a new control. Instead, an existing control will be updated with the dragged data source.

Create button to save changes
Then I will add a button to “Update Customer”. In the click event handler, I need to find current selected order, get the new CustomerID that replace the old FK value, and save the changes:

Order currentOrder = (Order)ordersViewSource.View.CurrentItem;

string customerID = customerIDComboBox.SelectedValue.ToString();

currentOrder.CustomerReference.EntityKey.EntityKeyValues[0].Value = customerID;

northwindEntities.SaveChanges();

I made the data context (northwindEntities) and colletion view source (ordersViewSource) to be the property in MainWindow class, to make the code simple. I need to drill down into the order, find the CustomerReference, and set the EntityKeyValue.

F5
Debug the application now. Everything looks good at first, but when I change the customer from one to another in the drop-down combobox, Bang! I get an InvalidOperationException: The property 'CustomerID' is part of the object's key information and cannot be modified. Although I successfully created the lookup binding, I cannot modify the value because the SelectedValue is not the FK in Order, but the PK in Customer!

This tells me that the combobox should be bound OneWay. I locate the combobox in XAML, and updated the SelectedValue binding mode:

SelectedValue="{Binding Path=Customer.CustomerID,Mode=OneWay}"

F5 again
Now change selection in the combobox will not trigger the exception anymore. I click on the “Update Customer” button to continue testing, bang (again)! This time, it says EntityKey values cannot be changed once they are set. I don’t believe there’s no way to do it, so I searched around the web and found that although the value cannot be reset, the entity key can be. Now let’s assign the FK value in this way:

currentOrder.CustomerReference.EntityKey = new System.Data.EntityKey(

currentOrder.CustomerReference.EntityKey.EntityContainerName+"."+currentOrder.CustomerReference.EntityKey.EntitySetName,

"CustomerID", customerID);

You could also use KeyValuePair if you have a complex key composed of more than one property. For more information, please see here.

After all the steps above, my application finally works as I expected. You might ask, why don’t you just include the key to save all these coding and bang (runtime exception)? Some developers, as I said, vote for not including FK in the conceptual model because FK is a relational database concept and it will pollute the object world. Another reason is, the feature of include FK is only available in Visual Studio 2010, targeting .NET framework 4.0, for newly added tables. If you have an existing entity data model, you would still need to get yourself used to the life without FK.

Please let me know if you have any other idea or question about this!

Cheers!

[Update 10/20]: Alex has a post talking about this too. He suggested a way of wrapping the entity key so that a FK property is added into the partial class. Please check it out here: https://msdn.microsoft.com/en-us/vbasic/bb735936.aspx

Comments

  • Anonymous
    October 19, 2011
    The comment has been removed