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

Question

I ran into an issue trying to update a foreign key field:

record.ForeignId = newId;

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

I was not the first to hit this issue:

LinqToSQL Error : Operation is not valid 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 it, amongst others.

Their solution is this:

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

That, of course causes a DB lookup on Foreign just to get an object back that has the Id I already know! So, how do I accomplish this update without the pointless query (or queries if I have lots of these FKs)?

Answer 1

You could new up an instance of the parent (with the correct Id), Attach it to the datacontext as the existing record state, then assign the Parent property of your child object.

Here's some code:

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

Answer 2

It doesn't solve the issue, but one way to partially get around the extra load is to check to see if the existing value is different than the new value. If it is different, you'll incur the query. Something like --

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