1

My scenario is little bit wierd, I have a list of entities, say i have ten items in list which will go to three different tables , associated to each other. I am using Linq 2 Sql and I need to insert it in a single hit instead of multiple iterations.

Is this possible. I have heard, in BLtoolkit there is InsertBatch() method that performs a bulk insert. Anything similar in L2S.

Bumble Blee
  • 79
  • 1
  • 10
  • Linq-to-SQL is **not** indended for batch operations. Use [the `SqlBulkCopy` class](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) for bulk inserts - it's much better suited for the task – marc_s Aug 28 '11 at 18:28

2 Answers2

2

There is InsertAllOnSubmit. With Linq To SQL, you just set the properties you want to change for an update. Then calling SubmitChanges will do the rest for you..

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
  • 1
    InsertAllOnSubmit is basically just calling InsertOnSubmit each time. Does NOT do a bulk insert. – Pleun Aug 29 '11 at 08:43
1

In short: No, it is not possible.

InsertAllOnSubmit is basically just calling InsertOnSubmit each time. So that does not help a lot.

If you profile the generated SQL, you will see that you will get a lot of individual insert statements resulting in a lot of overhead. Regardless of using InsertOnSumbit or InsertAllOnSumbit.

If you google around, you will see some attempts to add SqlBulkCopy behaviour to Linq-2-sql. For example: http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx

However, I think you might be better of implementing SqlBulkCopy yourself for your batch jobs.

Pleun
  • 8,856
  • 2
  • 30
  • 50
  • 1
    The actual database interaction actually happens on SubmitChanges. InsertOnSubmit and InsertAllOnSubmit just queue the unit of work changes for when the submission occurs. When we do interact with the database, however each request is sent to the database separately (to allow for transactional roll-back on a per record basis). Agreed on SqlBulkCopy/SSIS/Ado.Net TableParameters (http://msdn.microsoft.com/en-us/library/bb675163.aspx) as better options for bulk inserts. – Jim Wooley Aug 29 '11 at 15:07
  • Good addition - since you wrote the book I would not expect anything else :) – Pleun Aug 29 '11 at 15:32