How is InsertAllOnSubmit () correct and is it better than the InsertOnSubmit () loop?

Question

Let's say I have:

using (SomeDataContext db = new SomeDataContext()) { foreach(Item i in Items) { DbItem d = new DbItem; d.value = i.value; //.... etc ... db.InsertOnSubmit(d); } db.SubmitChanges(); } 

Is it possible and / or better (worse?):

 using (SomeDataContext db = new SomeDataContext()) { IEnumerable<DbItem> dbItems = //???? possible? foreach(Item i in Items) { DbItem d = new DbItem; d.value = i.value; //.... etc ... dbItems.Add(d); // ???? again, somehow possible? } db.InsertAllOnSubmit(dbItems); db.SubmitChanges(); } 

Answer 1

Well, you can use List<T> :

 using (SomeDataContext db = new SomeDataContext()) { List<DbItem> dbItems = new List<DbItem>(); foreach(Item i in Items) { DbItem d = new DbItem; d.value = i.value; //.... etc ... dbItems.Add(d); } db.InsertAllOnSubmit(dbItems); db.SubmitChanges(); } 

Whether it will be more effective or not, I do not know.

Answer 2

You won’t see a performance improvement here because nothing happens until you call SubmitChanges() .

But even if you were doing something ugly like this

 foreach(Item i in Items) { DbItem d = new DbItem; d.value = i.value; //.... etc ... db.InsertOnSubmit(d); db.SubmitChanges(); } 

I would not expect a significant decrease in performance. This is because LINQ-to-SQL performs special inserts.

Even if you use InsertAllOnSubmit , you will still see a separate SQL command to insert each .

Answer 3

Not to say that it is better or worse, but at any time you can make one database call or call the database in a loop, you will see an increase in performance. Now what .SubmitChanges() does behind the scenes is something beyond our control.