Entity Framework - Rollback for Mix of Stored Procedures and Entities

Question

I need to call a few stored procedures (data inserts) plus update an entity value to the database.

I want these operations to be atomic, so if an error occurs, they all rollback.

In my code, if I force an exception to observe the behavior of the rollback, my entity changes are rolling back, but my stored procedure changes do not get rolled back.

Looking at the implementation for unit of work, it's just looking at _context.ChangeTracker.Entries() - I assume completely ignoring any stored procedure changes?

When I change the unit of work implementation to use

new System.Transactions.TransactionScope()

the rollback works as expected.

Should I be using new System.Transactions.TransactionScope()? Are there any gotchas?

Code

...
using (var scope = _unitOfWork.Begin())
{
    try
    {                    
        var outcome = _context.Database.ExecuteSqlCommand("SOME EXEC COMMAND", parameters);        

        throw new Exception("Something happened");

        scope.Commit(); // Never gets called because of Exception                  
    }
    catch (Exception e)
    {
        scope.Rollback();
    }
}

Unit of work:

public class UnitOfWork : IUnitOfWork, ITransaction
{
    private readonly DbContext _context;
    private bool _committed;

    public UnitOfWork(DbContext context)
    {
        _context = context;
    }

    public ITransaction Begin()
    {
        _committed = false;
        return this;
    }

    public void Commit()
    {
        _context.SaveChanges();
        _committed = true;
    }

    public void Rollback()
    {
        foreach (var entry in _context.ChangeTracker.Entries())
        {
            switch (entry.State)
            {
                case EntityState.Modified:
                    entry.State = EntityState.Unchanged;
                    break;
                case EntityState.Added:
                    entry.State = EntityState.Detached;
                    break;
                case EntityState.Deleted:
                    entry.Reload();
                    break;
                case EntityState.Detached:
                    break;
                case EntityState.Unchanged:
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }
        }
    }

    public void Dispose(bool disposing)
    {
        if (!disposing) 
            return;

        if (!_committed)
        {
            Rollback();
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
}

Answer 1

Well yes - the Change Tracker of course can only track whatever you've changing / modifying / adding through means of the DbContext class.

Running a stored procedure is outside the scope of the EF Change Tracker - so if you base your "rollback" on simply things in the Change Tracker, you won't be able to properly handle anything your stored procedure have done.

Using TransactionScope is fundamentally different - this is an "umbrella" over all the database operations - including any stored procedures being executed - since it's basically on the database's level. So rolling back based on the transaction scope will roll back all database operations - whether handled via the EF DbContext, or via other means.