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)
{
//exception occurs here
_dataContext.Tasks.Attach(task, GetForKeyTable(task.KeyTable));
_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.
Update
I have made my repository implement IDisposable and changed everywhere that calls the constructor use the using (TaskRepository taskRepository = new TaskRepository). In the Dispose() method of TaskRepository, I have called Dispose() on my data context.
I have also changed the Update() method to call Detach() on my Task object. My code now looks like this:
public class TaskRepository : IRepository, IDisposable
{
private DataContextDataContext _dataContext;
public TaskRepository()
{
_dataContext = new DataContextDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Task>(t => t.Project);
dlo.LoadWith<Task>(t => t.Priority);
_dataContext.LoadOptions = dlo;
}
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)
{
task.Detach();
_dataContext.Tasks.Attach(task, true); //exception occurs here
_dataContext.Refresh(RefreshMode.KeepCurrentValues, task);
_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;
}
#region IDisposable Members
public void Dispose()
{
_dataContext.Dispose();
}
#endregion
}
The Detach() method on Task is this:
public void Detach()
{
this._Project = default(EntityRef<Project>);
this._Priority = default(EntityRef<Priority>);
}
For reference, my entities look like this:
I now get the following exception at the line indicated.
Cannot add an entity with a key that is already in use.
