Dealing with foreign key with ADO.NET Entity Framework and Linq-to-Entities

Read this article in your language IT | EN | DE | ES

We all know by now that table foreign keys are not directly accessible in Entity Framework and that searching on the subject on Google or Bing brings confusion and despair, at least it did to me. MSDN was not better, so I decided to go the trial and error route instead and I was not sorry.

I have found that, as with the .NET Framework, Linq often has a 1 liner solution to a problem, so my attitude was simple: find it. And so I did.

The trick is resolved with a simple and non-obscured object and it looks like this:

Entity.Reference.EntityKey = new EntityKey("Entities.Entity", "ReferencedColumn", "Value"));

Oddly enough, and contrary to what I have read, this command works for inserting, editing and deleting the foreign key. There are some caveats though, if you want to be able to delete the key, you’ll have to have the field / property nullabe in both the database and object.

Here is how I typically handle an insert, edit, delete situation:

private void Save(int ID)
        {
            try
            {

                int _id = Convert.ToInt32(ID);

                using (ObjectEntities db = new ObjectEntities())
                {
                    //Add a new object
                    if (_id == 0)
                    {
                        var _object = new object();

                        _object.Name = this.txtName.Text.Trim();

                        if (String.IsNullOrEmpty(this.SomeDropDown.SelectedItem.Value) == false)
                        {
                            _object.EntityReference.EntityKey = new 
                                    EntityKey("ObjectEntities.Entity", "ID",
                                        Convert.ToInt32(this.SomeDropDown.SelectedItem.Value));
                        }
                        else
                        {
                            _object.EntityReference.EntityKey = null; ;
                        }

                        //Some additional variable assignement

                        db.AddToEntity(_object);
                        db.SaveChanges();

                        // Rebuild the Grid

                        // return to the list
                    }
                    else
                    //Edit the object
                    {
                        var _object = ((from Item in db.Entity
                                        where Item.ID == _id
                                        select Item)
                                      .FirstOrDefault());

                        _object.Name = this.txtName.Text.Trim();

                        if (String.IsNullOrEmpty(this.SomeDropDown.SelectedItem.Value) == false)
                        {
                            _object.EntityReference.EntityKey = new    
                                    EntityKey("ObjectEntities.Entity", "ID",
                                        Convert.ToInt32(this.SomeDropDown.SelectedItem.Value));
                        }
                        else
                        {
                            _object.EntityReference.EntityKey = null; ;
                        }

                        db.SaveChanges();

                        // Reload the current item with the changes 
                    }
                }
                // Do some logging 
            }
            catch (Exception ex)
            {
                //Output some message about the error.
            }
        }

And that is all the is to it.

Related Links:

http://msdn.microsoft.com/en-us/library/dd283138.aspx 

http://msdn.microsoft.com/en-us/library/system.data.objects.dataclasses.ientitywithkey.entitykey.aspx