Should I call SaveChanges once or after each change?

Question

I need to make several changes in my database in my controller.

foreach (var valueStream in model.ListValueStream)
{
    ValueStreamProduct vsp = new ValueStreamProduct(valueStream.Id, product.Id);
    db.ValueStreamProduct.Add(vsp);
}
db.SaveChanges();

Should I call SaveChanges at the end or each time I make changes?

Answer 1

It depends.

1. With each change - If you want each save to run in its own transaction and be independent of other changes then run the save in the loop or after you make a change. Note that if there is a failure later in the code then the changes that have already occurred are persisted and will not be rolled back. This also has a higher performance cost as you are making more round trips to the data store. There are situations which could warrant this use though, here are 2 quick examples:
   1. You want to track progress of long running action back to the data store and include changes up until that point.
   2. You want to save in batches for large chunks of data that the code is processing and the code knows how to pick up an action after the last save point in the event of failure.
2. After all changes - If you want more performance and you want all saves to either pass or fail and be rolled back if there is any failure then run them at the end outside the loop or at the end of the code block. Now if there is a failure in the code (anywhere before or on the save changes call) no changes will be persisted to the store. In most situations this is desirable behavior as it ensures a good state for your data store. More frequently than not this is probably what you want your code to do.

Answer 2

It depends, on your requirement

Requirement 1) three table are independent.

public ActionResult Create() 
{ 
     db.ValueStreamProduct.Add(vsp);
     db.tbl.Add(tbl2);
     db.tbl.Add(tbl3);

     // only one time you can call savechanges()
     db.SaveChanges();
 }

Requirement 2) ValueStreamProduct table Id is necessary for tbl2.

But, you need to get your last inserted record's Id for second table. code like this

 public ActionResult Create() 
 {
     db.ValueStreamProduct.Add(vsp); 
     db.SaveChanges();

     // getting last inserted record's Id from ValueStreamProduct table.
     tbl2.Column = vsp.Id

     db.tbl.Add(tbl2);
     db.tbl.Add(tbl3);

     db.SaveChanges();
  }