LINQ to SQL inserting large object from .NET?

You could: MyContext.Items. InsertAllOnSubmit( from I in list select new Item { //map properties }) Ps. Haven't tried inserting that much data though.

You could: MyContext.Items. InsertAllOnSubmit( from I in list select new Item { //map properties }); Ps. Haven't tried inserting that much data though.

1 That will be very slow. LINQ-to-SQL will generate single insert statements for each record. – Kirk Broadhurst Aug 15 '09 at 14:44.

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.

Trying to work on this issue myself, as InsertOnSubmit for my 160,000 records has taken all day so far. – Dave Arkell Jul 8 '09 at 14:25 Let me know if you get stuck... – Marc Gravell? Jul 8 '09 at 14:56.

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).

Speak up, I cant hear you. – leppie Mar 13 '09 at 9:42.

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.

1 Even for non-database, you can use bulk transfer via SqlBulkCopy; there are lots of ways of creating an IDataReader, not just from a db. – Marc Gravell? Mar 13 '09 at 10:27.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.