How to bulk insert data into db using Linq to SQL?

Question

In the past, I always use Class SqlBulkCopy to complete bulk insert. But I don't know how to implement it using Linq to SQL. If inserted one by one, Efficiency will be very low.
Any good ideas?
Thanks in advance and sorry for my poor English.

score 4 · Accepted Answer · edited May 23 '17 at 10:27

4

Simple: you don't. You just use SqlBulkCopy. LINQ-to-SQL is simply a tool. SqlBulkCopy is a tool. Use the right tool for each job. Sometimes that means using something that isn't LINQ-to-SQL. This might mean creating a DataTable (or a spoof IDataReader if you are feeling ambitious) to represent the data; look perhaps at Convert generic List/Enumerable to DataTable? to get from your typed objects to a DataTable you can feed to SqlBulkCopy.

edited May 23 '17 at 10:27

Community

1
1

answered Oct 07 '11 at 12:18

Marc Gravell

1,026,079
266
2,566
2,900

1

+1. O/R mappers serve a specific need. Dont't abuse a tool. SqlBulkCopy is unbeatable for bulk inserts. – TomTom Oct 07 '11 at 12:24

score 0 · Answer 2 · answered Oct 07 '11 at 12:08

0

See this article on SubmitChanges().

Your changes are not transmitted to the server until you explicitly call SubmitChanges on the DataContext.

answered Oct 07 '11 at 12:08

user807566

2,828
3
20
27

2

Submitchanges does not do bulkinsert, but one insert statement per record. – Pleun Oct 07 '11 at 12:20
@Pleun: Thanks, I didn't realize. – user807566 Oct 07 '11 at 17:34

How to bulk insert data into db using Linq to SQL?

2 Answers2