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).
What would be the fastest way to insert the rows with least performance impact?
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
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:
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