CODEWITHSUNDEEP
c#linq-to-sqlienumerable
MetaGuru
MetaGuru

Reputation: 43873

How to properly InsertAllOnSubmit() and is that better than looping InsertOnSubmit()?

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?) to do:

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();
}

Upvotes: 8

Views: 9240

Answers (3)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28728

You won't see any 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 wouldn't expect any significant performance decrease. This is because LINQ-to-SQL does singular inserts.

Even if you use InsertAllOnSubmit, you'll still see a separate SQL Command for each row being inserted.

Upvotes: 4

kjennings.dev
kjennings.dev

Reputation: 51

Not saying that it is better or worse but anytime you can make a single database call vs. calling a database in a loop you will see a performance increase. Now what .SubmitChanges() does behind the scenes is something out of our control.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1502376

Well you could 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 that will be any more efficient or not, I don't know.

Upvotes: 13

Related Questions