Entity Framework: insert with identity column fails "sometimes" with OptimisticConcurrencyException

Question

Hi all,

While developing & learning with Entity Framework, I'm having a curious problem with inserts when I run the tests for one entity in my model. In concrete, the problem is when running some tests together. I'll explain myself:

I have one entity in my model called "DtoCategoria", with 2 members: id:Int32 and name:string, mapped to one table where id is an identity column. This is ok and quite simple.

I have a Data Access Layer for this DTO, called CadCategoria, where I have created a method for making inserts, like:

public class CadCategoria
{
    protected readonly CUENTASEntities bd = Singletons.bd;

    public bool add(EntityObject entity)
    {
        try
        {
            bd.AddObject(EntitySet, entity);
            return bd.SaveChanges() > 0;
        }
        catch (Exception e)
        {
            bd.Detach(entity);
            return false;
        }
    }

    // and some other methods... update, delete, etc.
}

and some other generic methods as well for updates and deletes. I share the context through the tiers in the app with a singleton pattern, like this:

public class Singletons
{
    public static CUENTASEntities bd;

    static Singletons()
    {
        bd = new CUENTASEntities();
    }
}

This looks fine since now. But, I created some unit tests to see if every thing is allright, which look like:

[TestClass]
public class CadCategoriaTest
{
    private CadCategoria bd = new CadCategoria();    

    [TestMethod]
    public void addTest()
    {
        var catAdd = new DtoCategoria { name= "cat 1" };
        Assert.IsTrue(bd.add(catAdd));

        bd.delete(catAdd);
    }

    [TestMethod]
    public void deleteTest()
    {
        var catDel = new DtoCategoria { name= "cat 2" };
        bd.add(catDel);

        Assert.IsTrue(bd.delete(catDel));
        Assert.IsFalse(bd.delete(new DtoCategoria { name= "not exists", id = -1 }));
    }

    [TestMethod]
    public void updateTest()
    {
        var a = new DtoCategoria { name= "cat 3" };
        bd.add(a);
        a.nombre = "name modified";
        Assert.IsTrue(bd.update(a));

        var b = bd.get(-1);
        Assert.IsFalse(bd.update(b));

        bd.delete(a);
    }
}

and now comes the extrange thing:

• When running the update test alone: Tests passed
• When running update & add tests: Tests passed
• When running update & delete tests: Update test failed and delete passed!

The error is in the 2nd line of the test: bd.add(a); that raises a OptimisticConcurrencyException in the SaveChanges method of the context.

Any idea why I have a concurrent exception with an insert with an identity column?? And only when combined with the delete test method?? It does not happen when combined with the add test, which also performs an "add" operation??

I'm afraid that if this fails in a test, it will also likely fail in the real app. It does not happen with another DTO which does not have an identity column id as PK.

Any ideas? Thanks a lot!!!

Sergi

Answer 1

Well, I think I found the issue at last. It had nothing to do with the Singleton pattern. The fact is that my delete test tries to delete and object that does not exists, just to check if the method returns false:

Assert.IsFalse(bd.delete(new DtoCategoria { name= "not exists", id = -1 }));

but in the bd.delete method, I forgot to dettach the unexisting object from the context, so the next time I tried to do context.SaveChanges(), it was triyng to delete again the wrong object, so it raised again the OptimisticException, as no rows where deleted.

This is my bd.delete method:

public bool delete(EntityObject entity)
    {
        try
        {
            if (entity.EntityKey == null)
                entity.EntityKey = bd.CreateEntityKey(EntitySet, entity);
            if (entity.EntityState == EntityState.Detached)
                bd.AttachTo(EntitySet, entity);
            bd.DeleteObject(entity);
            return bd.SaveChanges() > 0;
        }
        catch (Exception e)
        {
            bd.Detach(entity);
            return false;
        }
    }

The solution was the bd.Detach(entity); command in the catch section, to tell the context to forget about this mock entity. A quick look to SQL profiler gave me the answer.

This was why order matters: delete then update caused update to crash. Leaving delete as the last one, worked fine, 'cause there were no more SaveChanges() to the context.

Thanks for your patiente, @Craig, and sorry for the inconveniences.

Sergi.