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.

ShyamK · Accepted Answer · 2013-08-16 20:37:28Z

3

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.

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

answered Aug 16, 2013 at 20:37

ShyamK

452 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Flowerking · Accepted Answer · 2012-02-01 14:08:16Z

2

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

answered Feb 1, 2012 at 14:08

Flowerking

2,5911 gold badge21 silver badges30 bronze badges

3 Comments

Yuck Over a year ago

+1 Reusing the context here is a good idea. Using a stored procedure is an even better way to go. LINQ doesn't do bulk INSERT/UPDATE well as it really isn't intended to.

frenchie Over a year ago

got it: no bulk operations in linq-to-sql; thanks for the tip on reusing the DC.

Pleun Over a year ago

linq-to-sql will add a transaction itsself for each call to SubmitChanges so as long as you call it only once the transactionscope is useless. Also using an insert sp inside the foreach will not change it into a bulkinsert. In that case you need to move to SqlBulkCopy

Taryn · Accepted Answer · 2013-05-27 16:30:08Z

0

List<Object> Obj = (Enter the List you want to pass)

foreach (var item in Obj)
{
    item.Get your desired value after the dot
}

edited May 27, 2013 at 16:30

Taryn

249k57 gold badges374 silver badges409 bronze badges

answered Feb 1, 2012 at 14:19

user1174458

431 silver badge8 bronze badges

Collectives™ on Stack Overflow

insert list of objects with linq-to-sql

3 Answers 3

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Related