Entity Framework: Using ExecuteSqlCommand to clear a table causes an exception because of the ObjectStateManager

Question

I an using EF5 Code First and needed a way to clear a table quickly. I therefore produced a very simple method to use ExecuteSqlCommand to provide a fast table clear. The code is below:

    public void FastClearTable(Type tableType)
    {
        Context.Database.ExecuteSqlCommand(
            string.Format("delete from {0}", PluraliseTableName(tableType.Name)));
    }

I use this to clear a table before recalculating it. Below I have listed a simplified version of my method below. Note that the key of the table is a string (i.e. not an identity key) so I am putting in new entries with the same keys as the entries deleted by the ExecuteSqlCommand.

public int ComputeNewTableContent( IRepository<MyClass> rep, IUnitOfWork uow)
{
     if ( rep.GetUntracked().Count() > 0)
           uow.FastClearTable( typeof(MyClass));

     --- compute new entries and call rep.Insert( newEntry) for each one ---
     uow.Commit();    //This calls DbContext.SaveChanges()
}

This works fine the first time but if I run it again I get an exception on the SaveChanges() command, which is called by uow.Commit(). The exception is:

Message="Exception has been thrown by the target of an invocation."

InnerException Message="The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges."

If found this archived MSDN post and at the bottom the person had the same problem, but there wasn't an answer. My view is that the memory version of the data is still present and does not know about the ExecuteSqlCommand. However I don't don't know enough about EF yet to know how to fix it.

I can of course get round it by using a 'normal' delete but I would appreciate someone explaining what is going on and how I could handle it. That would improve my knowledge of Entity Framework.

Thanks in advance.

Answer 1

I hadn't found a good answer to my question on using ExecuteSqlCommand safely in entity framework until I came across Julia Lerman's excellent book 'Programming Entity Framework: DbContext'.

She covers the use of ExecuteSqlCommand (page 226) but most importantly she mentions using the Reload command after ExecuteSqlCommand to update the local (page 138). That updates the in-memory entity from the database. You use it as follows:

    yourDbContext.Entry(TheTrackedEntityYouWantToUpdate).Reload();

This is exactly what I needed. Thanks Julia.

I my opinion Julia Lerman's DbContext book is a must for anyone planning a real-life application using Code First.