ASP.Net MVC 5 and SQL Transaction Isolation Levels

Question

Having an issue with the creation of a front end for a database. I began with a EF6 Code First from database. I created the controller and went to run a test on the create page, but ran into a big issue. It gave me the following error:

 System.Data.SqlClient.SqlException: Transaction failed in database 'DB' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.

The other thing that showed up was the code from the controller:

if (ModelState.IsValid)
{
    db.Characters.Add(character);
    await db.SaveChangesAsync();
    return RedirectToAction("Index");
}

It highlighted the await db.SaveChangesAsync(); as the error line. So now I am confused as to where to look to change the transaction level in the MVC code. Will it be the Web.config, the model that was created for the database, or the controller? I know that I would have to use the TransactionScope to change it.
Easier would be to change the default transaction scope to what the database is set up for. Just not sure where to make that change?

Answer 1

In the constructor of your EF context you can call the following passing in the IsolationLevel you want

_ContextTransaction = Database.BeginTransaction(isolationLevel)

Then by overriding the SaveChangesAsync in the context, you can commit the transaction

public override Task<int> SaveChangesAsync() {
    var result = base.SaveChangesAsync();
    _ContextTransaction.Commit();

}

Note that you would need to Commit the transaction for other Saving methods too, and you might also consider testing if the context is part of another transaction before starting a new one with BeginTransaction(isolationLevel)

To check for current transactions you can use System.Transactions.Transaction.Current