Writing to log table after transaction rollback in EF

Question

I need to write to the log table after transaction rollback in EF. The current code:

using (MyDataBase db = new DataBase())
{
    using (var dbContextTransaction = db.Database.BeginTransaction())
    {
        try
        {
            //Inserting several records to different tables
            var newEntity = new Entity();
            ...
            db.Entity.Add(newEntity);

            db.SaveChanges();
            db.up_StoredProcCall;
            dbContextTransaction.Commit();
        }
        catch (Exception ex)
        {
            dbContextTransaction.Rollback();

            var logEntry = new LogEntry();
            ...
            db.LogEntry.Add(logEntry);
            db.SaveChanges();
        }
    }
}

It does not work as EF caches all inserts internally and flushes them all along with a log record in a single transaction on:

db.LogEntry.Add(logEntry);
db.SaveChanges();

What is the best way to achive the goal:

Create a separate DbContext to insert a log record?
Discard changes via the ChangeTracker like described here or here or here? Personally I do not think it's a good idea as then I will not need to use transactions at all.
Any other options are much appreciated

On the SQL server side, if a transaction rolls back, it's usually doomed (not a figure of speech, that's the technical term for it), meaning no more data modification statements can execute, including any writes to a log table. Therefore, necessarily you need at least a separate transaction to log the failure. — Jeroen Mostert, Sep 06 '17 at 13:54
I'm not familiar with how EF works with transactions, exactly. If it's getting upset about `dbContextTransaction` still existing, store the exception in a local variable from the `catch`, then check for it and log outside the `using`. — Jeroen Mostert, Sep 06 '17 at 14:12
Transactions in EF only changes the transaction context of changes at SQL side. Normally it wraps all changes made before SaveChanges call in one trancation. Transaction context changes this for example to include a stored procedure's call in transaction. But it does nothing with ChangeTracker. So moving log table insert away from transaction scope will not help as all changes made in transaction will persists in the EF's cache (ChangeTracker) and will go to the DB on SaveChanges. Only moving log table insert to another DbContext will help. But probably there is more convenient solution? — Alterant, Sep 06 '17 at 14:21
"On the SQL server side, if a transaction rolls back ... meaning no more data modification statements can execute" - it is not true. Rollback will only discard all changes made within the transaction. But after the rollback you can write to the DB. — Alterant, Sep 06 '17 at 14:23
Whoops. You're right, I'm confusing this with *errors* that doom the transaction *before* it is rolled back. Not all errors cause a transaction to terminate (and not all doom it). After an explicit `ROLLBACK`, the transaction doesn't *exist* anymore, so forget all that. — Jeroen Mostert, Sep 06 '17 at 14:32

score 1 · Accepted Answer · answered Sep 06 '17 at 18:46

1

Close the DbContext with the failed SaveChanges() and write the log entry from a new one. AFAIK there's no way to abandon the pending changes in the DbContext.

eg

using (MyDataBase db = new DataBase())
{
    using (var dbContextTransaction = db.Database.BeginTransaction())
    {
        try
        {
            //Inserting several records to different tables
            var newEntity = new Entity();
            ...
            db.Entity.Add(newEntity);

            db.SaveChanges();
            db.up_StoredProcCall;
            dbContextTransaction.Commit();
        }
        catch (Exception ex)
        {
            dbContextTransaction.Rollback();
            db.Dispose();

            using (MyDataBase dbLog = new DataBase())
            {
              var logEntry = new LogEntry();
              ...
              dbLog.LogEntry.Add(logEntry);
              dbLog.SaveChanges();

            }

        }
    }
}

answered Sep 06 '17 at 18:46

David Browne - Microsoft

80,331
6
39
67

Thank you David! But is it required to explicitly dispose the first DbContext before creation of the new one? I suppose that the new DbContext should have no pending changes upon creation and calling SaveChanges on the new one should not be affected by the changes made in the fist context. It will disposed by .NET after leaving of the catch block. Are there any risks or disadvantages having two contexts open at the same time? – Alterant Sep 06 '17 at 19:08
1

Not required. But if you rollback the transaction and Dispose the DbContext before opening the new one you'll likely reuse the same pooled connection. – David Browne - Microsoft Sep 06 '17 at 19:10
By the way, just wondering, does creation of transaction have an impact on the methods like Entity.Add, Entity.Remove and update? Or it only changes the behavior of SaveChanges method? In other words can I create the transaction just before calling to SaveCahnges? Like this: create the context, do data changes, create the transaction, SaveChanges, call SP, Commit/Rollback? – Alterant Sep 06 '17 at 19:35
1

You can start the transaction just before the first SaveChanges(), but starting the transaction earlier isn't harmful. – David Browne - Microsoft Sep 06 '17 at 19:41
I understand that it will be no harm to start it earlier. Question was just for my understanding what exactly it does. I had two options in my mind: it can mark somehow objects in the ChangeTracker or it just changes the behavior of SaveChanges method. Now it's clear that the latter is true. Thanks a lot! – Alterant Sep 06 '17 at 19:53

Writing to log table after transaction rollback in EF

1 Answers1