I have a huge list of "flattened" objects in json format, and a somewhat complicated relational DB schema (with about 20 tables corresponding to a flattened object). I'm trying to automate the insertions of those flattened objects in my new relational database:
foreach (var flattenedObject in flattenedObjects)
{
_repository.Insert(flattenedObject).Wait();
//some time logging, etc
}
The Insert() method callsAddRangeAsync() and AddAsync() for a number of related objects in different tables.
Since the flattened objects are legacy, I'd say about 0.001% of them are malformed and will violate DB constraints - for example trying to insert a duplicate composite primary key in one of the tables.
I expect these rare errors, thus my idea is - wrap the whole Insert() operation in a transaction - if any piece of the operation is invalid, just don't insert anything and log the error, so I can modify the flattened object manually before trying again. Thus my code looks somewhat similar to this:
public async Task Insert(FlattenedObject fo)
{
using (var transaction = _context.Database.BeginTransaction())
{
try
{
//magical code that calls AddAsync for multiple tables
}
catch (Exception ex)
{
transaction.Rollback()
//logging
}
}
}
However, if an error occurs somewhere in my try block (I try to insert an object that violates a composite primary key) my whole context object becomes corrupt.
The object that caused the exception still remains in my DbContext and any following call to AddAsync() in a different transaction triggers a new exception.
I tried recreating my DbContext and repo for every new object in the foreach loop above - but even then if I query:
_context.ChangeTracker.Entries().Where(e => e.State != EntityState.Unchanged);
I see my old object is still in the new instance of dbContext.
Is there any (elegant) way to tell my context to reset all pending changes - so that I can put it in the catch block whenever an error occurs? I want everything that happens within my failed transaction to stay there and not leak.
The code below worked for me. However, if someone posts a cleaner solution (I still expect there to be something out of the box with EF) I will accept it.
private void ResetContextState() => _context.ChangeTracker.Entries()
.Where(e => e.Entity != null).ToList()
.ForEach(e => e.State = EntityState.Detached);
After years, maybe some of you are still looking, and by a miracle you are using EF Core 5.0. There is a new feature to clear the ChangeTracker :
dbContext.ChangeTracker.Clear();
Just call it whenever an update fails.
But do not forget that this is not at all the best practice. Microsoft recommends to create new dbContext for each request, as it is designed to have that short lifespan.
More information here : Microsoft EF Core 5.0: ChangeTracker.Clear Method
But bit more complexe, is to use the DbContextFactory. This can be useful when application code needs to create and dispose context instances manually.
More Information here : Microsoft EF Core 5.0 : DbContextFactory
Enjoy