LINQ to SQL inserting large object from .NET

Question

What is the fastest way to get a list object with a count of 100,000 records into SQL using LINQ to SQL?

Answer 1

You could:

MyContext.Items.InsertAllOnSubmit( 
    from i in list
    select new Item
    {
       //map properties
    });

Ps. Haven't tried inserting that much data though

Answer 2

LINQ2SQL does not support batch inserts. Look at running your own SQL, or use another framework that does support it (I recall the Entity Framework does, but I cant be sure).

Answer 3

You might want to specify exactly where is the source of your 100,000 records coming from.

If you are retrieving the records from a non-database source, the fastest way I know to insert the records is by using the InsertAllOnSubmit method. Of course, this means 100,000 insert statements in a transaction, but without further details there isn't much choice in the matter.

If you are retrieving the records from a database, the performance will be better if you call a stored procedure instead. For example, in your stored procedure:

INSERT INTO TableA
SELECT * FROM TableB

Then, you call the stored procedure with LINQ.

Answer 4

For mass data, the fastest way is to use something like SqlBulkCopy to push the data (via a crafted IDataReader) into a staging table (same layout, but no indexes/etc) - and then use SqlCommand to execute a stored procedure to push the data from the staging table into the real table. But note that LINQ-to-SQL isn't involved here (unless you use it to invoke the stored procedure).

You could try just using regular LINQ-to-SQL InsertOnSubmit - assess how big a problem the volume of data is before you try to optimise it; it might be "fast enough" even though sub-optimal.

You can create an IDataReader fairly easily; I often use the csv on from here. To create one from a list, you could borrow SimpleDataReader from here and override DoRead.