The underlying provider failed on Open / The operation is not valid for the state of the transaction

Question

Here is my code

public static string UpdateEmptyCaseRevierSet() {
    string response = string.Empty;
    using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope()) {
        using (var db = new Entities.WaveEntities()) {
            var maxCaseReviewersSetID = db.CaseReviewerSets.Select(crs => crs.CaseReviewersSetId).Max();
            var emptyCHList = db.CaseHistories.Where(ch => ch.CaseReviewersSetID == null && ch.IsLatest == true && ch.StatusID != 100).ToList();
            for(int i=0; i < emptyCHList.Count; i++) {
                var emptyCH = emptyCHList[i];
                var newCaseReviewerSET = new Entities.CaseReviewerSet();
                newCaseReviewerSET.CreationCHID = emptyCH.CHID;
                db.CaseReviewerSets.Add(newCaseReviewerSET);
                emptyCH.CaseReviewerSet = newCaseReviewerSET;
            }
            db.SaveChanges();
        }
        tran.Complete();
    }
    return response;
}

The exception occures on "db.SaveChanges()"

I saw in another post with the same error message something about "it seems I cannot have two connections opened to the same database with the TransactionScope block." but I dont think that this has anything to do with my case.

Additionally the number of records to insert and update in total are 2700, witch is not that many really. But it does take quite a lot of time to complete the for statement (10 minutes or so). Since everything happening within the for statement is actually happening in the memory can someone please explane why is this taking so long ?

Answer 1

You can try as shown below using latest db.Database.BeginTransaction API.

Note : use foreach instead of for

using (var db = new Entities.WaveEntities()) 
  { 
    using (var dbContextTransaction = db.Database.BeginTransaction()) 
       { 
         try 
          { 
            var maxCaseReviewersSetID = db.CaseReviewerSets.Select(crs => crs.CaseReviewersSetId).Max();
            var emptyCHList = db.CaseHistories.Where(ch => ch.CaseReviewersSetID == null && ch.IsLatest == true && ch.StatusID != 100).ToList();

            foreach(var ch in emptyCHList) {
               var newCaseReviewerSET = new Entities.CaseReviewerSet();
               newCaseReviewerSET.CreationCHID = ch.CHID;
               db.CaseReviewerSets.Add(newCaseReviewerSET);
              }

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