Entity Framework: Setting a Foreign Key Property

Question

We have a table that looks roughly like this:

CREATE TABLE Lockers 
{
  UserID int NOT NULL PRIMARY KEY (foreign key),
  LockerStyleID int (foreign key),
  NameplateID int (foreign key)
}

All of the keys relate to other tables, but because of the way the application is distributed, it's easier for us to pass along IDs as parameters. So we'd like to do this:

Locker l = new Locker { 
  UserID = userID, 
  LockerStyleID = lockerStyleID, 
  NameplateID = nameplateID 
};
entities.AddLocker(l);

We could do it in LINQ-to-SQL, but not EF?

Answer 1

You could make an extension method that constructs the entity based on these ID's.

Answer 2

I'm not sure that's exactly what I want to do driAn. I don't want to construct the entity and I don't want to request it from the database just to submit a single ID column back.

Answer 3

Using an EntityKey solves your problem ;)

alk.

Answer 4

This missing feature seems to annoy a lot of people.

• Good news: MS will address the issue with .NET 4.0.
• Bad news: for now, or if you're stuck on 3.5 you have to do a little bit of work, but it IS possible.

You have to do it like this:

Locker locker = new Locker();
locker.UserReference.EntityKey = new System.Data.EntityKey("entities.User", "ID", userID);
locker.LockerStyleReference.EntityKey = new EntityKey("entities.LockerStyle", "ID", lockerStyleID);
locker.NameplateReference.EntityKey = new EntityKey("entities.Nameplate", "ID", nameplateID);
entities.AddLocker(locker);
entities.SaveChanges();

Answer 5

What I've been doing to make things easy is adding the foreign key property myself in the partial class:

public int UserID
{
   get
   {
      if (this.User != null)
         return this.User.UserID;
   }
   set 
   {
      this.UserReference.EntityKey = new System.Data.EntityKey("entities.User", "ID", value);
   }
}

Answer 6

Hi all, another method if you don't mind 'polluting' you db schema is to add a computed column, e.g. if you had a foreign key field FK_Customer you could define a new computed column FK_Customer_Computed which has the expression FK_Customer. When you generate\update your edmx model the field will appear like a regular field that you can then reference from you entity object.

Or wait for EF4 :)

Answer 7

Thank you for all your posts. i got the insert function working. But how you query using a join with the foreign key?

Answer 8

hmm i'm not sure. Let's say i want to do this query in sql:

Select a.LockerStyleID from Locker a JOIN User b ON b.ID = a.USerID Where b.FirstName = 'John'

Since UserID is missing in the entity i won't be able to do the link with Linq to Entity. Any suggestion?