Bulk insert records and get their Id's after bulk insert in Entity Framework

c# entity-framework entity-framework-6 sql

Question

I want to bulk insert 100,000+ rows into a table (which has a identity column which auto increments every time a new row is inserted).

  1. What would be the fastest way to insert the rows with least performance impact?

  2. After all rows are inserted, how do I get the list of ids of the rows that are recently inserted?

Note: I did the following but I did not get the list of ids

ctx.Documents.AddRange(documentsList);
ctx.SaveChanges();

var ids = documentsList.Select(d => d.Id);

Thanks

1
0
11/1/2017 9:17:38 PM

Expert Answer

There is no Bulk Insert in Entity Framework. You must use a third-party library to perform this kind of operation.

By default, Entity Framework will make a database round-trip for every entity to save. So more than 100,000 database round-trip will be required in your scenario which is INSANELY slow.


Disclaimer: I'm the owner of Entity Framework Extensions

This library is not free but allows you to perform all bulk operations including BulkInsert and return automatically the Id's. You can also insert related entity with IncludeGraph options:

  • Bulk SaveChanges
  • Bulk Insert
  • Bulk Delete
  • Bulk Update
  • Bulk Merge

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

EDIT: Answer questions

Are those the same bits as "EF Plus"

I'm also the owner of Entity Framework Plus

In short, we split free and paid features into two libraries. EF+ doesn't support Bulk Operations.

The only features both libraries currently share is Batch Delete and Batch Update

0
11/2/2017 1:13:29 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow