Linq to sql add/update in different methods with different datacontexts

Question

I have to methods, Add() and Update() which both create a datacontext and returns the object created/updated.

In my unit test I call first Add(), do some stuff and then call Update(). The problem is that Update() fails with the exception:

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

I understand the issue but want to know what to do about it? I've read a bit about how to handle multiple datacontext objects and from what I've heard this way is OK.

I understand that the entity is still attached to the datacontext in Add() but I need to find out how to solve this?

Thanks in advance

Answer 1

In order to update an entity that is attached to another data context, you will first need to detach it from the context and then attach it to the other context. One way to detach an object is as follows:

object ICloneable.Clone()
    {
        var serializer = new DataContractSerializer(GetType());
        using (var ms = new System.IO.MemoryStream())
        {
            serializer.WriteObject(ms, this);
            ms.Position = 0;
            return serializer.ReadObject(ms);
        }
    }

Answer 2

You would have to call Attach() on the update context's table instance for the the entity returned from Add(), eg updateContext.Products.Attach(product).

If attaching succeeds then you can update the entity and LINQ to SQL will 'know' to perform an update rather than an insert.

Bear in mind that trying to attach an entity can throw all sorts of issues, particularly if you are trying to attach an object graph and not just a single object.

I would just load up the new entity into the update context from the database using SingleOrDefault() or similar, then work on that, gives you the same functionality but much less buggy.

Answer 3

To be blunt, it's wrong from a design perspective to be spinning up DataContext instances in Add and Update methods.

Presumably these methods are in some kind of Repository class - in which case, the repository should be initialized with a pre-existing DataContext, generally passed in through the constructor.

If you design your repository this way, you don't have to worry about this problem:

public class FooRepository
{
    private MyDataContext context;

    public FooRepository(MyDataContext context)
    {
        if (context == null)
            throw new ArgumentNullException("context");
        this.context = context;
    }

    public void Add(Foo foo)
    {
        context.FooTable.InsertOnSubmit(foo);
    }

    public void Update(Foo foo)
    {
        Foo oldFoo = context.FooTable.Single(f => f.ID == foo.ID);
        oldFoo.Bar = foo.Bar;
        oldFoo.Baz = foo.Baz;
    }
}

Then, from whence you perform your updates:

Foo fooToSave = GetFooFromWherever();
using (MyDataContext context = new MyDataContext(...))
{
    FooRepository repository = new FooRepository(context);
    repository.Save(fooToSave);
    context.SubmitChanges();
} // Done

This pattern can be used and reused, and you can combine multiple repositories into a single "transaction"; you'll never run into any problems with it. This is how the DataContext, which encapsulates a Unit-of-Work pattern, was actually meant to be used.

Incidentally, when designing a repository, it's common to try to abstract away the cumbersome Insert/Update semantics and just expose a Save method:

public void Save(Foo foo)
{
    if (foo.ID == default(int))
    {
        Insert(foo);
    }
    else
    {
        Update(foo);
    }
}

That way you're not always having to worry about whether or not you've already inserted your Foo.

It is possible to coerce Linq to SQL into dealing with detached entities, but good luck getting it to deal with entities that are already attached to a different context. And even in the detached case, it's really quite cumbersome, you need to have timestamp fields on all your tables/entities or start messing with the version check/autosync properties - it's not worth it IMO, just design your repositories to use one context per instance and to share context instances between each other.

Answer 4

It's only OK if you don't reuse the object. Once an entity instance object is associated with a data context, information about that data context is added for change tracking purposes. If you try to reuse that object in a different data context, you get the error you got.

If you're working with different data contexts, it is advisable to create a new entity object and perform the add within that first data context, then retrieve that record using the other data context and do the updates with the new entity object.

The reasoning is that there is no way to guarantee that the information won't change between operations if you only use a single entity object and multiple data contexts. Each data context tracks and manages the changes based on the state of the database object at the time the entity object was created.