none
Updating an entity using Linq-to-SQL - attaching an entity that is not new RRS feed

  • Question

  • I am trying to write a program that uses Linq-to-SQL to interface with the database (MS SQL Server 2008). Adding and deleting seem to be ok but I can't get my head around updates.

    The entity has a version column on it which is a timestamp column on the database and used for the optimistic locking that comes built in to Linq-to-SQL. I have set the Update Check property for all of the fields on the entity to be Never.

    I have the following SaveTaskCommand which is used to insert and update entities, depending on whether or not the particular task has been added to the database already.

    public class SaveTaskCommand : CustomCommand
    {
      private Task _task;
      private TaskDetailsViewModel _taskDetails;
    
      public SaveTaskCommand(Task task, TaskDetailsViewModel taskDetails)
      {
        _task = task;
        _taskDetails = taskDetails;
      }
    
      public override void Execute(object parameter)
      {
        TaskRepository taskRepository = new TaskRepository();
        if (!taskRepository.ContainsTask(_task))
        {
          taskRepository.AddTask(_task);
          _taskDetails.Mediator.NotifyColleagues(ViewModelMessages.TaskAdded, _task);
        }
        else
        {
          taskRepository.UpdateTask(_task);
          _taskDetails.Mediator.NotifyColleagues(ViewModelMessages.TaskAmended, null);
        }
      }
    
      public override bool CanExecute(object parameter)
      {
        return _task.IsValid();
      }
    }
    
    

    The CustomCommand class is just a class that wraps up an ICommand and deals with the CanExecuteChanged event so that I didn't have to repeat the code in each of the commands.

    As you can see a TaskRepository is created in the Execute() method of the command which firstly checks if the task is already in the database and then chooses whether to insert or update. The code for the TaskRepository is below.

    public class TaskRepository : IRepository
    {
      private DataContextDataContext _dataContext;
    
      public TaskRepository()
      {
        _dataContext = new DataContextDataContext();
      }
    
      public List<Task> GetAllTasks()
      {
        return _dataContext.Tasks.ToList();
      }
    
      public Task GetForKeyTable(int keyTable)
      {
        return _dataContext.Tasks.Where(t => t.KeyTable == keyTable).FirstOrDefault();
      }
    
      public void AddTask(Task task)
      {
        task.Project = _dataContext.Projects.SingleOrDefault(p => p.KeyTable == task.KeyProject);
        _dataContext.Tasks.InsertOnSubmit(task);
        _dataContext.SubmitChanges();
    
      }
    
      public void UpdateTask(Task task)
      {
        _dataContext.Tasks.Attach(task, GetForKeyTable(task.KeyTable)); //exception occurs here
        _dataContext.SubmitChanges();
      }
    
      public void DeleteTask(Task task)
      {
        _dataContext.Tasks.Attach(task, GetForKeyTable(task.KeyTable));
        _dataContext.Tasks.DeleteOnSubmit(task);
        _dataContext.SubmitChanges();
      }
    
      public bool ContainsTask(Task task)
      {
        return GetForKeyTable(task.KeyTable) != null;
      }
    }
    
    

    At the line indicated, I get the following exception:

    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.

    I don't understand why I get this exception when I am passing in the original version of the entity. I get the same exception if I change the commented line to

    _dataContext.Tasks.Attach(task, true);
    

    Any help would be greatly appreciated.

    Sunday, May 8, 2011 10:50 AM

Answers

  • Hello Stuart Leyland,

     

    Welcome to the LINQ to SQL Forum.

    According to your description, I think the reason you got the exception is when you attach original version of the entity, you haven't detached it. Actually, you should do it. An explain from Matt Warren in this thread:

    "The intended use of the Attach API is to specify instances of objects that were deserialized from another tier, not to use instances that are already associated with the DataContext.  When you queried for the old version you not only got an instance that was already part of the DataContext you introduced that object's primary key into the DataContext's cache, so even if you copied that original object's data into a new instance you still would not be able to call Attach since the primary key for the object is already in use."

    Here are some solutions:

    http://omaralzabir.com/linq_to_sql__how_to_attach_object_to_a_different_data_context/

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

    I hope these can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 10, 2011 7:33 AM
    Moderator