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

c# entity-framework entity-framework-6 transactionscope

Question

The code is below.

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 error is encountered during "db.SaveChanges()"

I saw in a different post with the identical problem message that there was"it seems I cannot have two connections opened to the same database with the TransactionScope block." However, I don't believe that this is relevant to my situation.

Additionally, there are just 2700 records to add and change in total, which is not a lot. But completing the for statement does take a long time (10 minutes or so). Can someone explain why this is taking so long given that everything that occurs inside the for statement really occurs in memory?

1
2
9/23/2016 11:21:56 AM

Accepted Answer

You may use the most recent db.Database.BeginTransaction API to attempt as indicated below.

usage zzz-13 zzzforeach instead offor

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(); 
        } 
 } 
} 
2
9/23/2016 11:21:22 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow