Linq to SQL: How to Attach object to a different data context

After upgrading to Visual Studio 2008 RTM, you will have trouble
updating Linq to SQL Classes which are read from one data context
and then updated into another data context. You will get this
exception during update:

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.

Here’s a typical example taken from a “http://forums.microsoft.com/msdn/ShowPost.aspx?postid=2524396&siteid=1”>
Forum post:

   1: public static void UpdateEmployee(Employee employee)
   2: {
   3:   using (HRDataContext dataContext =new HRDataContext())
   4:   {
   5:     //Get original employee
   6:     Employee originalEmployee = dataContext.Employees.Single(
e=>e.EmployeeId==employee.EmployeeId);
   8:     //attach to datacontext
   9:     dataContext.Employees.Attach(employee, originalEmployee);
  11:     //save changes
  12:     dataContext.SubmitChanges();
  14:   }
  15: }

When you call the Attach function, you will get the exception
mentioned above.

Here’s a way to do this. First, create a partial class that adds
a Detach method to Employee class. This method will
detach the object from it’s data context and detach associated
objects.

   1: public partial class Employee
   2: {
   3:   public void Detach()
   4:   {
   5:     this.PropertyChanged = null; this.PropertyChanging = null;
   7:     // Assuming there's a foreign
key from Employee to Boss
   8:     this.Boss = default(EntityRef<Boss>);
   9:     // Similarly set child objects to default as well
  11:     this.Subordinates = default(EntitySet<Subordinate>);
  12:   }
  13: }
Now during update, call Detach before attaching the

object to a DataContext.

   1: public static void UpdateEmployee(Employee employee)
   2: {
   3:     using (HRDataContext dataContext = new HRDataContext())
   4:     {
   5:         //attach to datacontext
   6:         employee.Detach();
              dataContext.Employees.Attach(employee);
   9:         //save changes
              dataContext.SubmitChanges();
  12:     }
  13: }
This'll work. It assumes the employee object already has its

primary key populated.

You might see during update, it’s generating a bloated UPDATE
statement where each and every property is appearing on the WHERE
clause. In that case, set UpdateCheck to Never for
all properties of Employee class from the Object Relational
Designer.

18 thoughts on “Linq to SQL: How to Attach object to a different data context”

  1. Hi omar,

    out of curiosity, are you using linq in any big projects so far?

    Best Regards,

    Andreas

  2. Omar, it would be better if you could place the souce codes with IDE colors. There are some free tools available on the web. Thanks

  3. I corrected the code. There was a mistake on this line:

    this.PropertyChanged = this.PropertyChanging = null;

    Each of these need to be individually set to null.

    this.PropertyChanged = null;

    this.PropertyChanging = null;

  4. Andreas:

    Not yet. I am not yet convinced Linq to SQL is good for multitier environment. DataContext holds reference to each and every Linq object it produces in order to track changes via PropertyChanging event. Event handlers are by-directional strong reference. That means both the object and the data context are holding reference to each other all the time. It’s scary!

  5. I have an Address object, which has an AddressType object, a state object, and a country object. These child objects are loaded through Get methods that populate them through other data contexts.

    When I new an Address object, assign it its properties, and try to save it, I get the exception mentioned. Are you saying I have to detach the AddressType, State and Country objects separately?

    1) This is crazy.

    2) I could have multiple key relationships for State (for instance). Are you saying I have to default every single foreign key?

    3) It still doesn’t seem to work, I can’t save a new address without getting the exception.

  6. Also, this error is throw on build:

    Cannot implicitly convert type ‘System.ComponentModel.PropertyChangingEventHandler’ to ‘System.ComponentModel.PropertyChangedEventHandler’

    on this line:

    this.PropertyChanged = this.PropertyChanging = null;

  7. Hi Omar,

    Just bought your book and it looks ace thanks.

    I was wondering if you could elaborate on the LINQ multitier solution you came up with in there. You're using XMLSerialisation to detach the object, then reform it without any attachments (because the contract was disregarded during the serialisation process).

    Is this actually a better way than this post – which I can't get to work either way! For example, in your book code you've passed an object directly to XMLSerialiser which you need to pass the type instead etc.

    Rob

  8. Elad, I dont know what your case is but for most people, its the attach that blows up. So you dont even get to define what your RefreshMode is. RefreshMode is also meant for resolving insert, update conflicts. In any case, Omar, you method works except that you have to detach, attach “before” making the changes that needs to be submitted. Most people were making the changes before detaching and attaching in which case, the context still doesnt know about the changes.

  9. Hi,

    could you post the aspx code?

    I have a problem with a formview, when I try to send the entity object as a objectdatasource updateparameter, it is passed as empty object.

    Thanks in advance,

    Jason

  10. Hi Omar,

    I have spet a lot of time to find the best solution for this trouble:

    Before u starting update just simple get the detached employee object.

    Employee GetDetachedEmpolee(Employee emp_to_edit)

    {

    dataContext db = new dataContext();

    db.DefferesLoadingEnabled = false; //important!!!

    return db.Employees.Where(e=>e.ID == emp_to_edit.ID).SingleOrDefault();

    }

  11. The site very professional! Keep up the good work! Oh yes, one extra comment – maybe you could add more pictures too! So, good luck to your team!

  12. PLINQO implements Detach on all of the entities by default as well as a TON of other features and enhancements. Check it out at PLINQO.com.

  13. The problem is that if the main entity include entityset, the data in the entityset will not update un the DB.

    Have you a solution for that.

    Thanks!

  14. I tried the detach() method, It updates only the parent object(employee) and child objects(subordinates) are never updated because they are discarded when you call detach method. Any Idea, how to attach a multi tier object where the complete object hierarchy is updated, as is expected ?

  15. You can use the netdomain framework to detach an object including all aggregated objects.
    The usage is straightforward.

    var ws = new LinqToSqlWorkspace(new MyDataContext());
    var person = ws.CreateQuery().Where(p => p.Name == name).First();
    ws.Detach(person);

    Cheers,
    Roger

    netdomain is a light-weight framework for developing Domain-Driven Design applications in .Net.

    netdomain supports developer in creating flexible data access layers according to Domain-Driven Design pattern.

    The main features of netdomain are:

    * Simplifies the using of persistence frameworks (ORM)
    * Enables and simplifies Test Driven Development
    * Supports LINQ to SQL, ADO .Net Entity Framework 4.0 and NHibernate
    * Expandable by any data source supporting Linq
    * Enhances missing functionalities to LINQ to SQL and Entity Framework
    * Supports high performance mass data processing

Leave a Reply to A fantastic site, and brilliant effort. Cancel reply