LINQ to SQL ForeignKeyReferenceAlreadyHasValueException error

Question

This error is being generated when I try to change a foreign key. I know this is a very common error I’ve found tons of information about it and tried to implement the fixes I’ve found but I still get this error when trying to update Keys. Reference Thread

Originally I was just directly assigning the value and not trying to map the entities.

ticket.assigned_to_group = assigned_to

I’ve since changed to try and map the entities which I believe the correct answer; however I still get the error.

ticket.assigned_to_group = db.sub_units.Single(f => f.id == assigned_to).id;

Any idea why this would not work. Also if I have a table with multiple foreign keys, do I really need to do a new query for each key or is there a better way?

Answer 1

I think you are trying to assign an ID when you need to assign the entity. (I'm not 100% sure this is what you are doing)

// Don't assign just the id
Person.ParentId = parentId;

// Assign the entity
Person.Parent = db.Parents.Single(x.Id == parentId);

Answer 2

For your second question , you can load all the sub_units by

var groups = db.sub_units.ToArray() and once in memory do something like this:

foreach(var ticket in tickets)
{
    ticket.assigned_to_group = groups.Single(f => f.id == assigned_to);
}