12

This is my coding. It shows error like An error occurred while starting a transaction on the provider connection. See the inner exception for details.

       DemoEntities db = DemoEntities.CreateNewDemoEntity();
       var query = (from f in db.Product_Table
                    where f.ReportID == reportID && f.StateID == stateID
                    select f);
       foreach(var q in query)
       {
           Custom_Search_Transformation cst = new Custom_Search_Transformation()
           {
               CustomerID = customerID,
               StateID = stateID,
               FullProductID = q.FullProductID
           };
           db.Custom_Search_Transformation.AddObject(cst);
           db.SaveChanges();
       }
Golda
  • 3,823
  • 10
  • 34
  • 67

2 Answers2

17

The db.SaveChanges(); should come outside of the foreach loop:

DemoEntities db = DemoEntities.CreateNewDemoEntity();
var query = (from f in db.Product_Table
             where f.ReportID == reportID && f.StateID == stateID
             select f);
foreach(var q in query)
{
    Custom_Search_Transformation cst = new Custom_Search_Transformation()
    {
        CustomerID = customerID,
        StateID = stateID,
        FullProductID = q.FullProductID
    };
    db.Custom_Search_Transformation.AddObject(cst);          
}
db.SaveChanges();
Marcos Dimitrio
  • 6,651
  • 5
  • 38
  • 62
Golda
  • 3,823
  • 10
  • 34
  • 67
  • Done the same but still getting same error. One thing is that I am running it in a thread – Moeez Jun 06 '20 at 07:00
17

Make your Queryable lists to .ToList() it should work fine.

zaza
  • 365
  • 2
  • 7
  • 3
    Sometimes you have to save data to the DB at the end of Each Loop. If this is the case, as this answer explains it "var q in query.ToList" solves the problem. – Rabskatran Jul 22 '15 at 13:09
  • I had this issue, and I have to save the data during each iteration to grab the ID out of a record to use for tracking purposes with a 3rd-party system. Running a `.ToList()` before looping allowed me to iterate over those items without keeping the transaction open, making the `SaveChanges` call work no problem. – Grungondola Oct 22 '19 at 17:16