The transaction operation cannot be performed because there are pending requests working on this transaction.

Question

The goal is to create ~18 WPackage entries via the loop:
foreach (var dbitem in dbCList)

Each WPackage could have:
-- 1+ activities
--each activity would have 1+ resource
--1+ budgets
--1+ Signatures
--1+ CostCodes

Our schema or model diagram would be:
WPackage
--Activities
-----Resources (child of Activities)
--CostCodes
--Budgets
--Signatures

The following code fails on:
dbContextTransaction.Commit();
"The transaction operation cannot be performed because there are pending requests working on this transaction."

 [HttpPost]

        public ActionResult Copy([Bind(Include = "ID,WBSID,...***fields excluded for brevity")] Package model)
        {
            if (ModelState.IsValid)
            {
                try
                {
  using (var dbContextTransaction = db.Database.BeginTransaction())
                    {

                        var dbCList = db.Packages.Join(db.WBS,
                            ***expression ommitted for brevity
                        //this dbClist will build about 18 items in the collection for below loop


                        foreach (var dbitem in dbCList)
                        {


                            int testWPID = dbitem;
                            WPackage prvWP = db.WPackages.Find(dbitem);


                            int previousWPID = dbitem;
                            WPackage previousWP = db.WPackages.Find(dbitem);
                            model.ID = dbitem;


                            db.WPackages.Add(model);
                            db.SaveChanges();

                            var budgets = db.Budgets.Where(i => i.WPID == previousWPID);
                            foreach (Budget budget in budgets)
                            {
                                budget.WPID = model.ID;
                                db.Budgets.Add(budget);

                            }

                            var costCodes = db.CostCodes.Where(i => i.WPID == previousWPID);
                            foreach (CostCode costCode in costCodes)
                            {
                                costCode.WPID = model.ID;                                
                                db.CostCodes.Add(costCode);
                            }

                            var activities = db.Activities.Where(i => i.WPID == previousWPID);
                           **code excluded for brevity

                                var previousActivityID = activity.ID;

                                db.Activities.Add(activity);
                                db.SaveChanges();

                                var resources = db.Resources.Where(i => i.ActivityID == previousActivityID);
                                foreach (Resource resource in resources)
                                {
                                    resource.WPID = model.ID;                                    
                                    resource.ActivityID = activity.ID;
                                    resource.ActivityNumber = activity.ActivityNumber;

                                    db.Resources.Add(resource);
                                    db.SaveChanges();

                                }

                            }


                            var signatures = db.RolesAndSigs.Where(i => i.KeyId == previousWPID && i.Type == "WPL").OrderBy(i => i.Role).OrderBy(i => i.Person);
                            foreach (RolesAndSig signature in signatures)
                            {
                               db.RolesAndSigss.Add(signature);
                            }

                            db.SaveChanges();
                            dbContextTransaction.Commit();
}

I've also tried to have the Commit() run outside the foreach dbitem loop like:

 db.SaveChanges();
   //dbContextTransaction.Commit();



                        }
                        dbContextTransaction.Commit();

...but this returns error of:
[EXCEPTION] The property 'ID' is part of the object's key information and cannot be modified.

Answer 1

Issue was I needed to create a new model instance on each loop inside:
foreach (var dbitem in dbCList)

Seems to be working now...thank you!

Answer 2

Hi CC-8522,
This may be because Entity Framework automatically wraps all update, insert, and delete operations in a transaction by default.
As this document said, in all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction.
So you don't have to create your own transaction context.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.