none
To Attach or Not To Attach: What should I be doing? RRS feed

  • Question

  • So this seems to be a lot harder than it should be, to me. I'm trying to create some new records via Linq to SQL. I have a one -> many relationship between two of my objects (tables?) and the new record I'm trying to create is a child, in this case. A few things.

    1. How should I bet setting the parent on this child? Should I set it by Id or by object?
    2. This is probably dependent on 1, but before I SubmitChanges() should I Attach() to the parent object? Otherwise it seems like it would try to insert it?

    As it stands rightnow, if I try to attach I get the following exception:

    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.

    If I try to SubmitChanges() without attaching I get the following exception:

    DuplicateKeyException: Cannot add an entity with a key that is already in use.

    Anyone have any ideas what's goin' on here? I'm kinda damned if I do, damned if I don't as of right now...

    Edit: So it seems like if I do the following, everything goes just fine:

    var child = new Child();
    child.ParentId = parent.Id
    childRepository = new ChildRepository();
    childRepository.Save(child);
    Wednesday, April 21, 2010 2:25 AM

Answers

  • Hello Brad,

     

    If you just want to set the child’s FK column to the PK of any existing parents entities in the database, I would recommend you directly set the FK column in the child entity and call SubmitChanges() later.  

     

    LINQ to SQL does not support attach an entity from another DataContext, http://www.west-wind.com/weblog/posts/162336.aspx.   If you really want to attach the parent entity, please try to detach it first.  There are some workarounds to detach the LINQ to SQL entities, like the references below suggested:

    http://geekswithblogs.net/michelotti/archive/2007/12/25/117984.aspx

    http://omaralzabir.com/linq_to_sql__how_to_attach_object_to_a_different_data_context/

     

    Hope there are helpful to you!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 21, 2010 9:34 AM
    Moderator
  • I ran into the same problem on Friday. A Google search found that I wasn't alone on this, and I still don't think there is a solid "Best Practice" available. Here is the solution that worked for me.

    I am just getting started with Linq to SQL after using NHibernate the last several years, so running into this was frustrating. I watched the Nerd Dinner MVC tutorial at www.asp.net/mvc. What I didn't see in their example was setting a field to a foreign key (probably need to re-watch because they have two tables - Dinners joined to RSVPS). I have two tables, Services and AppUser. In the Services table, I have a column to track who created the record. The intellisense shows the field in the Services table as the name they are in the database, but the data type is long and I was expecting a type of Appuser. I then notice there is another field in the Services intellisense called Appuser11. So I decided I would load up the Appuser object for the user that is creating the record and set the service.Appuser11 property. Using the nerd dinner example, I placed each separate table in a separate SQL Repository class that provided the CRUD data access operations:

        // Services controller reference to the service repository
        private IServiceRepository _repository;
    
        public ServiceController() 
        {
          _repository = new SqlServiceRepository();
        }
    
    /* What I was trying to do when creating the record was set the Appuser object on the create method */
        public ActionResult Create(Service service)
        {
          if (ModelState.IsValid)
          {
            try
            {
              service.DateCreated = DateTime.Now;
              if (User.Identity.IsAuthenticated)
              {
                Appuser appuser = new SqlAppuserRepository().GetAppuser(User.Identity.Name);
                if (appuser != null)
                {
                  service.Appuser11 = appuser;
                }
    
              }
              _repository.Add(service);
              return RedirectToAction("Index");
            }
            catch
            {
              return View(service);
            }
          }
          else
          {
            return View(service);
          }
        }
    

     This is when I got the same exception:

    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.

    Working with NHibernate, I knew that if I used a different logic class to load a model object, and I wanted to include the loaded model I had to pass the session from the saving model's logic class. Based on this, I refactored my SQL Repository classes to accept a DataContext parameter:

      public class SqlAppuserRepository : IAppuserRepository
      {
        DB db;
        public SqlAppuserRepository()
        {
          db = new DB();
        }
    
        public SqlAppuserRepository(DB _db)
        {
          db = _db;
        }
    

    I then refactored the IServiceRepository to include the DataContext object (I have since refactored again to create an IDataContext interface because the premise of the Fake Repository for the unit tests is to not need a DataContext):

      public interface IServiceRepository
      {
        DB DataContext { get; }
        IQueryable<Service> FindAllServices();
        Service GetService(long id);
        void Add(Service service);
        void Update(Service service);
        void Delete(Service service);
      } 

    And added a public property to the SqlServiceRepository to expose the DataContext object:

        public DB DataContext
        {
          get { return db; }
        }

    I then modified the Services controller create method to pass the DataContext to the SqlCategoryRepository:

        public ActionResult Create(Service service)
        {
          if (ModelState.IsValid)
          {
            try
            {
              service.DateCreated = DateTime.Now;
              if (User.Identity.IsAuthenticated)
              {
                Appuser appuser = new SqlAppuserRepository(_repository.DataContext).GetAppuser(User.Identity.Name);
                if (appuser != null)
                {
                  service.Appuser11 = appuser;
                }
    
              }
              _repository.Add(service);
              return RedirectToAction("Index");
            }
            catch
            {
              return View(service);
            }
          }
          else
          {
            return View(service);
          }
        }
    

    And it works like a charm! I did find a blog post about a request scoped pattern. I haven't refactored to follow this pattern, but I have used NHibernate in a similar fashion with good success.

    http://blogs.vertigo.com/personal/keithc/Blog/archive/2007/06/28/linq-to-sql-and-the-quote-request-scoped-datacontext-quote-pattern.aspx

    Hope this helps explain my resolution to the NotSupportedException!

    Russell Davis

    Monday, April 26, 2010 4:39 AM

All replies

  • Hello Brad,

     

    If you just want to set the child’s FK column to the PK of any existing parents entities in the database, I would recommend you directly set the FK column in the child entity and call SubmitChanges() later.  

     

    LINQ to SQL does not support attach an entity from another DataContext, http://www.west-wind.com/weblog/posts/162336.aspx.   If you really want to attach the parent entity, please try to detach it first.  There are some workarounds to detach the LINQ to SQL entities, like the references below suggested:

    http://geekswithblogs.net/michelotti/archive/2007/12/25/117984.aspx

    http://omaralzabir.com/linq_to_sql__how_to_attach_object_to_a_different_data_context/

     

    Hope there are helpful to you!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 21, 2010 9:34 AM
    Moderator
  • Hi Brad,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, April 26, 2010 2:17 AM
    Moderator
  • I ran into the same problem on Friday. A Google search found that I wasn't alone on this, and I still don't think there is a solid "Best Practice" available. Here is the solution that worked for me.

    I am just getting started with Linq to SQL after using NHibernate the last several years, so running into this was frustrating. I watched the Nerd Dinner MVC tutorial at www.asp.net/mvc. What I didn't see in their example was setting a field to a foreign key (probably need to re-watch because they have two tables - Dinners joined to RSVPS). I have two tables, Services and AppUser. In the Services table, I have a column to track who created the record. The intellisense shows the field in the Services table as the name they are in the database, but the data type is long and I was expecting a type of Appuser. I then notice there is another field in the Services intellisense called Appuser11. So I decided I would load up the Appuser object for the user that is creating the record and set the service.Appuser11 property. Using the nerd dinner example, I placed each separate table in a separate SQL Repository class that provided the CRUD data access operations:

        // Services controller reference to the service repository
        private IServiceRepository _repository;
    
        public ServiceController() 
        {
          _repository = new SqlServiceRepository();
        }
    
    /* What I was trying to do when creating the record was set the Appuser object on the create method */
        public ActionResult Create(Service service)
        {
          if (ModelState.IsValid)
          {
            try
            {
              service.DateCreated = DateTime.Now;
              if (User.Identity.IsAuthenticated)
              {
                Appuser appuser = new SqlAppuserRepository().GetAppuser(User.Identity.Name);
                if (appuser != null)
                {
                  service.Appuser11 = appuser;
                }
    
              }
              _repository.Add(service);
              return RedirectToAction("Index");
            }
            catch
            {
              return View(service);
            }
          }
          else
          {
            return View(service);
          }
        }
    

     This is when I got the same exception:

    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.

    Working with NHibernate, I knew that if I used a different logic class to load a model object, and I wanted to include the loaded model I had to pass the session from the saving model's logic class. Based on this, I refactored my SQL Repository classes to accept a DataContext parameter:

      public class SqlAppuserRepository : IAppuserRepository
      {
        DB db;
        public SqlAppuserRepository()
        {
          db = new DB();
        }
    
        public SqlAppuserRepository(DB _db)
        {
          db = _db;
        }
    

    I then refactored the IServiceRepository to include the DataContext object (I have since refactored again to create an IDataContext interface because the premise of the Fake Repository for the unit tests is to not need a DataContext):

      public interface IServiceRepository
      {
        DB DataContext { get; }
        IQueryable<Service> FindAllServices();
        Service GetService(long id);
        void Add(Service service);
        void Update(Service service);
        void Delete(Service service);
      } 

    And added a public property to the SqlServiceRepository to expose the DataContext object:

        public DB DataContext
        {
          get { return db; }
        }

    I then modified the Services controller create method to pass the DataContext to the SqlCategoryRepository:

        public ActionResult Create(Service service)
        {
          if (ModelState.IsValid)
          {
            try
            {
              service.DateCreated = DateTime.Now;
              if (User.Identity.IsAuthenticated)
              {
                Appuser appuser = new SqlAppuserRepository(_repository.DataContext).GetAppuser(User.Identity.Name);
                if (appuser != null)
                {
                  service.Appuser11 = appuser;
                }
    
              }
              _repository.Add(service);
              return RedirectToAction("Index");
            }
            catch
            {
              return View(service);
            }
          }
          else
          {
            return View(service);
          }
        }
    

    And it works like a charm! I did find a blog post about a request scoped pattern. I haven't refactored to follow this pattern, but I have used NHibernate in a similar fashion with good success.

    http://blogs.vertigo.com/personal/keithc/Blog/archive/2007/06/28/linq-to-sql-and-the-quote-request-scoped-datacontext-quote-pattern.aspx

    Hope this helps explain my resolution to the NotSupportedException!

    Russell Davis

    Monday, April 26, 2010 4:39 AM