CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6
user3861992
user3861992

Reputation: 319

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

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

Upvotes: 3

Views: 5318

Answers (2)

geeves
geeves

Reputation: 141

Another library can do this now as well: https://github.com/borisdj/EFCore.BulkExtensions

It achieves it using the SetOutputIdentity BulkConfig option. After insertion the C# objects in your list will have the IDs set to the values generated by the database. Under the hood BulkExtensions also uses an efficient bulk copy implementation so that insertion is fast.

Upvotes: 0

Jonathan Magnan
Jonathan Magnan

Reputation: 11337

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

Upvotes: 1

Related Questions