What’s the difference between using Savechanges() only once after adding data via a foreach and using inside the foreach?

Question

I’m entering an average of 50,000 records into the database via the Entity Framework. And I got a question regarding the following code placements: in the first (1) placement, I use a foreach creating the objects with your data and adding them (contexto.addToTabela), using the SaveChanges() outside the foreach, at the end of the whole process, being used only once. The second (2) placement would be using the SaveChanges() within the foreach, that is, to every added object there is a commit. What is the difference between the two situations?

code example:

situation 1:

using (entities contexto = new entities())
{
    foreach (var item in lista)
    {
        ...
        contexto.AddToTabela(item);
    }

    contexto.SaveChanges();
}

situation 2:

using (entities contexto = new entities())
{
    foreach (var item in lista)
    {
        ...
        contexto.AddToTabela(item);
        contexto.SaveChanges();
    }
}

Answer 1

In logic: In situation 1 you are putting everything into a single transaction, if a line gives error, the rollback will be about everything. Already in situation 2, it will save up to the line that gives error, without a specific Try catch treatment it will stop the foreach.

In the performance: In situation 1 it will stack everything in memory, the 50 thousand record and send at once. Depending on the case, it can slow the process down a bit. In situation 2 it receives the data and already sends, there are many connections with the BD server. Setting up a third situation where he makes a Savechanges every 100 items can perform better than both. It doesn’t weigh much the memory and also doesn’t make so many unique requests with the BD.

If you can use Entity Framework 6, use the Addrange method: http://msdn.microsoft.com/en-us/library/system.data.entity.dbset.addrange%28v=vs.113%29.aspx