Databases
StackOverflow2013
Postsdbo
POInserting many rows in EF too slow, how to get primary key from SqlBulkCopy?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting many rows in EF too slow, how to get primary key from SqlBulkCopy?
    primarykey
    data
    text
    <p>We have a use case in our application where a user triggers a request that will cause an insert of 100 to 1000 rows.<br/> After that insert, we need the object to continue processing and create more objects which are foreign keys to the originally inserted objects, or in other words, we need the Primary Key ID of the inserted objects.</p> <p>So far we have used EF to do that in a foreach loop, this was too slow, and was taking about 15-20 seconds to complete for about 600 rows. (while blocking the user, bad :( )</p> <p>Original code (also handles updates, but we do not care about performance there, it is not blocking users):</p> <pre><code>foreach (Location updatedLoc in locationsLoaded) { // find it in the collection from the database Location fromDb = existingLocations.SingleOrDefault(loc =&gt; loc.ExtId.Equals(updatedLoc.ExtId)); // update or insert if (fromDb != null) { // link ids for update updatedLoc.Id = fromDb.Id; // set values for update db.Entry(fromDb).CurrentValues.SetValues(updatedLoc); } else { System.Diagnostics.Trace.WriteLine("Adding new location: " + updatedLoc.Name, "loadSimple"); // insert a new location &lt;============ This is the bottleneck, takes about 20-40ms per row db.Locations.Add(updatedLoc); } } // This actually takes about 3 seconds for 600 rows, was actually acceptable db.SaveChanges(); </code></pre> <p>So after researching on SO and internet, I found out I was using EF the wrong way and need to use <code>SqlBulkCopy</code></p> <p>And thus the code was rewritten, and what used to take ~20 seconds, now takes ~100ms (!)</p> <pre><code>foreach (Location updatedLoc in locationsLoaded) { // find it in the collection from the database Location fromDb = existingLocations.SingleOrDefault(loc =&gt; loc.ExtId.Equals(updatedLoc.ExtId)); // update or insert if (fromDb != null) { // link ids for update updatedLoc.Id = fromDb.Id; // set values for update db.Entry(fromDb).CurrentValues.SetValues(updatedLoc); } else { System.Diagnostics.Trace.WriteLine("Adding new location: " + updatedLoc.Name, "loadSimple"); // insert a new location dataTable.Rows.Add(new object[] { \\the 14 fields of the location.. }); } } System.Diagnostics.Trace.WriteLine("preparing to bulk insert", "loadSimple"); // perform the bulk insert using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["bulk-inserter"].ConnectionString)) { bulkCopy.DestinationTableName = "Locations"; for (int i = 0; i &lt; dataTable.Columns.Count; i++) { bulkCopy.ColumnMappings.Add(i, i + 1); } bulkCopy.WriteToServer(dataTable); } // for update db.SaveChanges(); </code></pre> <p><strong>The problem is</strong>, after the bulk copy, the objects in the <code>Locations</code> collection, which is part of the EF ORM are not changed (that is okay and expected), but I need the inserted ids to continue work on these objects.</p> <p>A simple solution would be to immediately select the data again from the database, I have a the data at hand, I can simply re-select it into a different collection.</p> <p><s>But that solution feels incorrect, is there no way to get the ids as part of the insert.</s></p> <p><strong>EDIT:</strong> the simple solution works, see accepted answer below on how to easily sync it back to EF.</p> <p>Maybe I should not use SqlBulkCopy (i expect up to about 1000 rows, no more) and use something else?</p> <p>Please note, A few related SO questions and solutions, all seem to move away from EF..</p> <ol> <li><a href="https://stackoverflow.com/questions/2945414/possible-to-get-primaykey-ids-back-after-a-sql-bulkcopy">Possible to get PrimayKey IDs back after a SQL BulkCopy?</a></li> <li><a href="https://stackoverflow.com/questions/6107206/improving-bulk-insert-performance-in-entity-frame-work">Improving bulk insert performance in Entity framework</a></li> <li><a href="https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework">Fastest Way of Inserting in Entity Framework</a> (this is about SaveChanges() performance with many pending inserts, should call it every X inserts and not at the end of the processing with 1000s pending)</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJonathan Levison
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInserting many rows in EF too slow, how to get primary key from SqlBulkCopy?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POInserting many rows in EF too slow, how to get primary key from SqlBulkCopy?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POInserting many rows in EF too slow, how to get primary key from SqlBulkCopy?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload