Entity Framework keeps throwing Duplicate Primary Key Error Despite Rollback

Question

Running on .NET Framework 4.6.2, I have a method that uses Entity Framework to insert a row into a SQL Server database table. It uses a transaction that gets rolled back if an error is thrown:

public async Task InsertReportRecord(Guid consultantReportId, string code, string studyId, string serverLocation)
{
    using (var transaction = _reportRepository.BeginTransaction(IsolationLevel.RepeatableRead))
    {
        try
        {
            var record = new Report()
            {
                ConsultantReportId = consultantReportId,
                Code = code,
                StudyId = studyId,
                RequestedOnUtc = DateTime.UtcNow,
                ServerLocation = serverLocation
            };

            _reportRepository.Add(record);
            await _reportRepository.SaveAsync();
            transaction.Commit();
        }
        catch (Exception e)
        {
            _logger.Error("DB Transaction error, rolling back: " + e);
            transaction.Rollback();
        }
    }
}

The issue is once it attempts to insert a duplicate row, I would expect it to just roll it back and continue on. Instead, it seems to corrupt the repository instance and cause all future attempts to insert a record to fail with the same error.

That is to say:

• Fail to insert row with ID 123, throws duplicate PK error (ID = 123) (currently expected)

• Attempt to insert a completely different row with ID 456 afterwards, this also fails and throws duplicate PK error (ID = 123), this is the part I would not expect

This continues on for hours until eventually the error latches on to a different ID to fail with.

I recognize that the root of this issue should be to avoid inserting duplicates in the first place, and I am looking into that. But I still feel this piece of code should be robust enough to shrug off an error without failing all future inserts.

I've verified the way this method is written with the transaction rollback logic against numerous posts and articles and it seems like it should be right, but I must be missing something if this is happening. Any help is appreciated!

Answer 1

The transaction rollback clears up the database - but not the EF (EF Core?) db context. This db context will still contain that row with ID=123, possibly in the "Added" state, so the next time you call .SaveChanges() on the context, EF will try to insert that row again.

You could reset the "offending" object's entity state to "Detached" - something like:

context.Entry(yourObject).State = EntityState.Detached;

to just "clear out" that one offending object from your change tracker, without having to recreate your DbContext.