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

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 ot开发者_如何学编程hers.

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)?

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;

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)
}

I had the same issue. I created a quick work-around, probably not the best solution(as it could affect performance), but it worked for me.

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

In my solution all I wanted to do (similar to you) was changed an account type ID to a different account type ID (in the database these are FKs) for a user.

As the DataContext was already loaded, it was not allowing the change. The work around?

I created a function where I create a new instance of the DataContext, run my LINQ query, submit the changes and then dispose the DataContext after.