linq-to-sql "an attempt has been made to attach or add an entity that is not new"?

Question

I've been getting several errors:

1. cannot add an entity with a key that is already in use

2. An attempt has been made to attach or add an entity that is not new, perhaps having been loaded from another datacontext

In case 1, this stems from trying to set the key for an entity versus the entity. In case 2, I'm not attaching an entity but I am doing this:

MyParent.Child = EntityFromOtherDataContext;

I've been using using the pattern of wrap everything with a using datacontext. In my case, I am using this in a web forms scenario, and obviously moving the datacontext object to a class wide member variables solves this.

My questions are thus 2 fold:

1. How can I get rid of these errors and not have to structure my program in an odd way or pass the datacontext around while keeping the local-wrap pattern? I assume I could make another hit to the database but that seems very inefficient.

2. Would most people recommend that moving the datacontext to the class wide scope is desirable for web pages?

Answer 1

For those that came after me, I'll provide my own take:

The error "an attempt has been made to add or attach an entity that is not new" stems from this operation:

Child.Parent = ParentEntityFromOtherDataContext

We can reload the object using the current datacontext to avoid the problem in this way:

Child.Parent = dc.Entries.Select(t => t).Where(t => t.ID == parentEntry.ID).SingleOrDefault();

Or one could do this

MySubroutine(DataContext previousDataContext) { work... }

Or in a web forms scenario, I am leaning to making the DataContext a class member such as this:

DataContext _dc = new DataContext();

Yes, the datacontext is suppose to represent a unit of work. But, it is a light-weight object and in a web forms scenario where a page is fairly transient, the pattern can be changed from the (using dc = new dc()) to simply using the member variable _dc. I am leaning to this last solution because it will hit the database less and require less code.

But, are there gotchas to even this solution? I'm thinking along the lines of some stale data being cached.

Answer 2

What I usually do is this

public abstract class BaseRepository : IDisposable
{
    public BaseRepository():
        this(new MyDataContext( ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString))
    {
    }

    public BaseRepository(MyDataContext dataContext)
    {
        this.DataContext = dataContext;
    }

    public MyDataContext DataContext {get; set;}

    public void Dispose()
    {
        this.DataContext.Dispose();
    }
}

Then imagine I have the following repository

public class EmployeeRepository : BaseRepository
{
     public EmployeeRepository():base()
     {
     }

     public EmployeeRepository(MyDataContext dataContext):base(dataContext)
     {
     }

     public Employee SelectById(Guid id)
     {
          return this.DataContext.Employees.FirstOrDefault(e=>e.Id==id);
     }

     public void Update(Employee employee)
     {
         Employee original = this.Select(employee.Id);
         if(original!=null)
         {
             original.Name = employee.Name;
             //others
             this.DataContext.SubmitChanges();
         }
     }
}

And in my controllers (I am using asp.net mvc)

public ActionResult Update(Employee employee)
{
    using(EmployeeRepository employeeRepository = new EmployeeRepository())
    {
        if(ModelState.IsValid)
        {
            employeeRepository.Update(employee);
        }
    }

    //other treatment
}

So the datacontext is properly disposed and I can use it across the same instance of my employee repository

Now imagine that for a specific action I want the employee's company to be loaded (in order to be displyed in my view later), I can do this:

public ActionResult Select(Guid id)
{
    using(EmployeeRepository employeeRepository = new EmployeeRepository())
    {
        //Specifying special load options for this specific action:
        DataLoadOptions options = new DataLaodOptions(); 
        options.LoadWith<Employee>(e=>e.Company);
        employeeRepository.DataContext.LoadOptions = options;

        return View(employeeRepository.SelectById(id));
    }
}

Answer 3

1. Linq to SQL is not adapted to disconnected scenarios. You can copy your entity to a DTO having a similar structure as the entity and then pass it around. Then copy the properties back to an entity when it's time to attach it to a new data context. You can also deserialize/reserialize the entity before attaching to a new data context to have a clean state. The first workaround clearly violates the DRY principle whereas the second is just ugly. If you don't want to use any of these solution the only option left is to retrieve the entity you're about to modify by its PK by hitting the DB. That means an extra query before every update. Or use another ORM if that's an option for you. Entity Framework 4 (included with .NET 4) with self-tracking entities is what I'm using currently on a web forms project and everything is great so far.

2. DataContext is not thread-safe and should only be used with using at the method level, as you already do. You can consider adding a lock to a static data context but that means no concurrent access to the database. Plus you'll get entities accumulated in memory inside the context that will turn into potential problems.