How to efficiently update a foreign key in LINQ to SQL / SQLMetal?

Question

I had a problem updating the foreign key field:

record.ForeignId = newId; 

It bombs "Operation is invalid due to the current state of the object" due to SQLMetal code that throws System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException() .

I was not the first to get into this problem:

LinqToSQL error: the operation is invalid due to the current state of the object and http://social.msdn.microsoft.com/forums/en-US/linqtosql/thread/f9c4a01a-195a-4f2b-a1cb-e2fa06e28b25/ discuss this in particular.

Their solution is as follows:

 record.Foreign = Database.Foreigns.Single(c => c.Id == newId); 

This, of course, does a DB search on Foreign just to return an object that has an identifier that I already know! So, how do I perform this update without a meaningless request (or requests if I have many such FKs)?

Answer 1

You can create an instance of the parent (with the correct identifier), Attach it in the datacontext as the existing state of the record, and then assign the parent property of your child.

Here is the code:

 int theId = 5; Parent p = new Parent() { Id = theId}; dc.Parents.Attach(p); child.Parent = p; 

Answer 2

It does not solve the problem, but one way to partially circumvent the additional load is to check if the existing value is not different from the new value. If this is different, you will be requested. Something like -

 if(obj.PropertyID != newValue){ obj.Property = PropertyClass.Load(newValue) } 

Answer 3

I had the same problem. I created a quick solution, maybe not the best solution (as this may affect performance), but it worked for me.

In my solution, I have a class with my DataContext declared at the class level, so I can use it in everything.

In my solution, everything I wanted to do (like you) was changed by the account type identifier to another account type identifier (in the database it is FK) for the user.

Since the DataContext has already been loaded, it did not allow the change. Work around?

I created a function in which I create a new instance of the DataContext, run my LINQ query, post the changes, and then delete the DataContext after.