Linq Db.SaveChanges use with ForEach Loop

Question

I have a Sql table with 12000 records and need to copy it to another table.

mset1 md = new mset1();
mset2 pd = new mset2();
foreach (var b in md.Myset) //dbset
{
    Obj m = new Obj()
    {
        coslat = b.coslat,//float
        Code = b.Code.Remove(b.Code.Length - 1),//string
        sinlat = b.sinlat,//float
        lat = b.lat,//float
        lon = b.lon,//float
        latrad = b.latrad,//float
        lonrad = b.lonrad //float
    };
    pd.Postcodeset.Add(m);
    pd.SaveChanges();
}

What I want to know when should I SaveChanges() efficiently. Should it be inside the For Loop or should it go outside the For Loop.

Edit: I am worried because I have 7 floats and the string Code is on average 10kb each. If I dont save changes inside the For Loop I might run out of memory.

What is your definition of efficient? Speed? Low memory consumption? It all depends. However, I would think that outside the loop is better, but that's having not tested it — Dan Drews, Sep 03 '13 at 11:38
@DanDrews I have 6 floating points and dont want to run out of memory. — Flood Gravemind, Sep 03 '13 at 11:42

Hexie · Accepted Answer · 2013-09-03T11:55:18.803

2

It would be best to place it outside the loop - this would mean that once the processing has completed, the data layer will be updated.

Something else to mention / a suggestion: Place the foreach code within a Try Catch and use the RollBack() in the case of an exception. This will "rollback" any change(s) made to the database and avoid having a negative impact - de.

RollBack Function

Hope this helps.

edited Sep 03 '13 at 11:55

answered Sep 03 '13 at 11:42

Hexie

3,955
6
32
55

score 0 · Answer 2 · answered Sep 03 '13 at 11:38

0

Place it outside the for loop.

You are adding to the collection (PostCodeSet) in your foreach, but only need to commit the collection once, therefore it makes sense to call SaveChanges once rather than n times.

answered Sep 03 '13 at 11:38

Darren

68,902
24
138
144

score 0 · Answer 3 · edited Sep 03 '13 at 12:00

0

You should move

pd.SaveChanges();

outside of the loop so you only make 1 call to the database.

edited Sep 03 '13 at 12:00

Uooo

6,204
8
36
63

answered Sep 03 '13 at 11:39

Haidar Abbas

192
4

score 0 · Answer 4 · answered Sep 03 '13 at 11:40

0

I would always put it outside the for loop. Not for performance reasons but because the changes will be done within a transaction.

I would expect it to be more efficient as well because your making less requests to the database.

answered Sep 03 '13 at 11:40

pingoo

2,074
14
17

score 0 · Answer 5 · edited May 23 '17 at 12:05

0

The benefit of doing just one SaveChanges once you have added multiple rows is that all of the updates will be done under a unit of work.

If high performance for bulk inserts is your primarly requirement, then I believe you have the wrong tool for the job - e.g. look at SqlBulkCopy instead.

edited May 23 '17 at 12:05

Community

1
1

answered Sep 03 '13 at 11:40

StuartLC

104,537
17
209
285

score 0 · Answer 6 · answered Sep 03 '13 at 11:42

0

You have to move this outside the loop.

It save all the change after adding data in pd.Postcodeset.

answered Sep 03 '13 at 11:42

Ajay

6,418
18
79
130

Linq Db.SaveChanges use with ForEach Loop

6 Answers6