insert list of objects with linq-to-sql

Question

I have a list of objects that I'm inserting in a database with linq-to-sql. At the moment, I'm using a foreach loop that adds each element and that looks roughly like this:

foreach (o in List<MyObjectModel)
{
  using MyDataContext
  {
     TheLinqToSqlTableModel TheTable = new TheLinqToSqlTableModel();

     TheTable.Fieldname = o.Fieldname;
     .... repeat for each field

     MyDataContext.TheTablename.InsertOnSubmit(TheTable);
     MyDataContext.SubmitChanges();

  }
}

Is there a better way to insert a list of objects in a DB using linq-to-sql?

Thanks for your suggestions.

Solution 1

Some quick improvements to consider..

a) Why creating datacontext for each iteration?

Using(Datacontext dc = new Datacontext())
{
    foreach(...)
    {

    }
   dc.SubmitChanges();
}

b) Creating a stored procedure for the Insert and using it will be good for performance.

c) Using Transaction Scope to undo insertions if any error encountered.

Using (TransactionScope ts = new TransactionScope())
   {
      dc.SubmitChanges();
   }

Solution 2

Why don't you use InsertAllOnSubmit() method.

Example:

IEnumerable<Post> _post = getAllNewPost();

 Using(Datacontext dc = new Datacontext())
    {
        dc.Posts.InsertAllOnSubmit(_post);
        dc.SubmitChanges();
     }

That's all you need.

////////////////////////