Transactions in Entity Framework

In EntityFramework, the DbContext is, for all intents and purposes, a working bucket of data which is being manipulated. You can "stage" any number of modifications by adding them to the context and, finally, calling the SaveChanges() method to persist them to your data source (eg. a database). If there is an issue with the submission, an exception will be thrown and all of the modifications are not submitted. So, in this situation, things are essentially inherently wrapped in a transaction. Here's a quick example from a business class:

private void processEnrollmentFile(string fileName, List<ParsedDataViewModel> result)
{
    //Create new file log
    var fileLog = new FileLog
    {
        FileDate = DateTime.UtcNow,
        FileName = fileName
    };
    fileLogRepository.Add(fileLog);

    //Create enrollment records
    foreach (var enrollment in result)
    {
        var record = new Enrollment
        {
            UserId = enrollment.AgentId,
            SocialSecurityNumber = enrollment.SocialSecurityNumber,
        };
        enrollmentRepository.AddEnrollment(record);
    }
    contextCoordinator.SaveChanges();
}

As you can see, first a file log is created and added to the context (via the fileLogRepository), enrollments are created and added to the context (via the enrollmentRepository), and everything is saved via an instance of a DataContextCoordinator class. That class is in the data access layer and wraps the context SaveChanges() method:

public class DataContextCoordinator : IDataContextCoordinator
{
    public int SaveChanges()
    {
        try
        {
            return context.SaveChanges();
        }
        catch (Exception ex)
        {
            logger.ErrorException(ex);
            throw;
        }
    }
}

Using the example above, if there is an issue creating one of the enrollment records, no enrollment records nor a file log are created. But what happens if we need to include something in our processEnrollmentFile() method that executes outside of the context? Here's an example of how that same method could be altered:

private void processEnrollmentFile(string fileName, List<ParsedDataViewModel> result)
{
    //Create new file log
    var fileLog = new FileLog
    {
        FileDate = DateTime.UtcNow,
        FileName = fileName
    };
    fileLogRepository.Add(fileLog);

    //Create enrollment records
    foreach (var enrollment in result)
    {
        var record = new Enrollment
        {
            UserId = enrollment.AgentId,
            SocialSecurityNumber = enrollment.SocialSecurityNumber,
        };
        enrollmentRepository.AddEnrollment(record);
    }
    contextCoordinator.SaveChanges();

    //Process enrollments
    fileLogRepository.ProcessEnrollments();
}

In the updated method, we're now calling the ProcessEnrollments() method of the fileLogRepository. Let's say this method calls a stored procedure in our database that processes the enrollments. What happens if that stored procedure fails? We've already created and saved the file log and enrollment records. How can we include the execution of the stored procedure in a transaction that includes our context modifications?

Let's first start by returning a result (1 if success, 0 if failed) from the stored procedure:

CREATE PROCEDURE [dbo].[usp_ProcessEnrollments]
@ReturnStatus int OUT
AS
--DO STUFF SET @ReturnStatus = 1; --Set to 0 if failed
END

And here is our repository method that calls it:

public void ProcessEnrollments()
{
    var response = new SqlParameter
    {
        ParameterName = "ReturnStatus",
        DbType = DbType.Int32,
        Direction = ParameterDirection.Output
    };
    var result = context.Database.SqlQuery<int>("EXEC usp_ProcessEnrollments @ReturnStatus", response);
    var wasSuccessful = result.First() == 1;
    if (!wasSuccessful)
    {
        var message = "An error occurred processing enrollments";        logger.Error(message);
        throw new BadRequestException(message);
    }
}

Now we have to somehow wrap our entire enrollment processing block (the context changes and the stored procedure call) in a transaction. We start by adding the following method to the DataContextCoordinator class in our data access layer which accepts an Action to execute (if you need to return something, use a Func):

public void ExcecuteTransaction(Action action)
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            action();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            logger.ErrorException(ex);
            transaction.Rollback();
            throw;
        }
    }
}

Finally, we wrap our processEnrollmentFile() method in the business class in an action which we pass into the new ExecuteTransaction() method:

public void UploadEnrollmentFile(string fileName, IList<ParsedDataViewModel> enrollmentData)
{
var processEnrollmentFileAction = new Action<string, IList<ParsedDataViewModel>>(processEnrollmentFile);
contextCoordinator.ExcecuteTransaction(() => processEnrollmentFileAction(fileName, enrollmentData));
}

Now if anything fails along the way, nothing will be persisted to the database. Additionally, this can be used in not just situations where calls are being made outside of your DbContext. If you need to call SaveChanges() on your DbContext numerous times for some reason, but want to undo everything if something goes wrong, this transaction solution can work for that as well.