Create new LINQ to Entities Objects with associations

Question

I'm trying to get my head around how to add new records to a table that have a foreign key to another table using LINQ to Entities. I'll try and explain:

Say I have a simple database with two tables. For example lets call one LibraryItem and the other LibraryItemStatus. The LibaryItem has a foreign key to the LibraryItemStatus table so an item can be assigned a status.

My question is how do I create a new LibraryItem using LINQ To Entities and associate it with a status? My code below must be wrong but I cannot find any examples to help.

LibraryEntities entities = new LibraryEntities();

LibraryItem item = new LibraryItem();
item.Name = "some name";
item.SomeAttribute = "x";
// What do I do here?
// This seems wrong as I don't want a new status.
item.Status = new Status() { id = 1 };
// This seems wrong as I don't really want to have to query the repository
item.Status = entities.Status.First(s => s.StatusID == 1);
// 
entities.AddToItems(item);
entities.Savechanges();

Answer 1

Check for an item.StatusReference property. That allows some discovery/setting of metadata on the relationship without actually querying for it.

Answer 2

This is the "right" way:

item.Status = entities.Status.First(s => s.StatusID == 1);

or:

item.Status = entities.GetObjectByKey(new EntityKey("MyContext.Status", "StatusID", 1));

...which will load from memory if the object is already loaded (entities.Status.First will always query the DB).

This is the "hacky but faster way":

item.StatusReference.EntityKey = new EntityKey("MyContext.Status", "StatusID", 1);

Good: Never any query. Bad: You can't reference item.Status; it will still be null.

Update: There will be a better way to do this in .NET 4.0.

Answer 3

You can also map the related table to your ItemEntity in the entitymodel.

I´ve done that for the ProductName in Product -> ProductLanguage in my project published on the url below.

openticket.codeplex.com