System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext

System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

You may have gotten this error when using LINQ to SQL. I personally hate this error, but surprisingly enough, out of 2 years of using LINQ to SQL, this is the first time I've gotten this (2 years later, I mean). This error happens in this scenario: imagine loading up an object Order. This object has a primary key relationship Customer, which you can access via:

Order.Customer

Under the scenes, the order class uses the EntityRef class to store the order. It's default value is default(EntityRef), which is it's default state (existing EntityRef reference, but null Customer reference within this class so no value gets assigned). Say you are accessing the customer in an order that already exists. When you do:

order.Customer

This reference gets loaded from the context, using the value from CustomerKey. if you assign a new value to Order.CustomerKey as in:

order.CustomerKey = 3;

The LINQ entity notices that the Customer object (underlying EntityRef _Customer variable) has already been loaded (through its HasLoadedOrAssignedValue property, or similarly named). Because you are now changing the key, this exception gets thrown because LINQ is setup not to allow you to do this. The way to change this is by doing:

order.Customer = this.DataContext.Customers.First(i => i.Key == 1);

You have to assign the reference to the Customer, and this instead updates the key, the reverse way around. I don't like this because this forces me to perform an additional data query, and in a business app this may reduce performance just to update a reference. I chose an alternate way. By adding a partial class for the order, I add a ClearCustomer method that does the following (note all LINQ types implement partial keyword, which means you can create another partial class to add additional methods).

public partial class Order ...
{
public void ClearCustomer()
{
_Customer = default(EntityRef);
}
}

And now, calling this method before changing the assignment clears the customer, and I can make the change. A bit of a hack, but it works. Unfortunately, because again the EntityRef is internal to the class, the method has to be in the class definition (can't use a static helper class to do this, at least easily). To update the reference, I can now do:

order.ClearCustomer();
order.CustomerKey = 7;

http://dotnetslackers.com/Community/blogs/bmains/archive/2009/05/21/linq-to-sql-error-foreignkeyreferencealreadyhasvalueexception.aspx