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).
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
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow