views:

430

answers:

4

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

A: 

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);
        }
    }
Randy Minder
I get an exception: System.Runtime.Serialization.SerializationException: Object graph for type 'xxx' contains cycles and cannot be serialized if reference tracking is disabled.. xxx is a the type of a property of the object being serialized
Oskar Kjellin
It's not advisable to reuse entities between data contexts, anyway. The entity object has change tracking built in that doesn't get updated if you move it to a new data context. You can create a new entity object and then do Attach(), because the new object has no change tracking footprints.
Neil T.
A: 

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.

Sam
I do actually have this code in the method trowing exception: Article article = db.Articles.First(a => a.ID == articleID); Thanks anyway!
Oskar Kjellin
+3  A: 

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.

Aaronaught
I started thinking about this, is it really okay to start mixing MSSQL specific objects like the datacontext in the business logic layer from a design point of view?
Oskar Kjellin
@Oskar: This isn't mixing anything; the whole idea of a repository is that you're abstracting the database, so you can just pass it around as an `IFooRepository` and have your domain logic depend on that. In my example I show manual construction of a data context and repository but in production, in a non-trivial system, you'd probably have these dependency-injected, and in addition to the `IFooRepository` abstraction you'd also have an `IUnitOfWork` or similar implementation that wraps the `context.SubmitChanges` method. I do this frequently in apps that support multiple data providers.
Aaronaught
Of course, in a throwaway app, or if you know you're never going to move away from SQL Server or Linq to SQL, then I wouldn't waste time on DI-ing the database-related stuff. Only you know the size, scope, and volatility of your project.
Aaronaught
A: 

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.

Neil T.