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