How to do fast bulk insert through EF on related SQL tables?

Question

I have a database which contains 2 tables:

Apartment: ID, Name, Address
Tenant: ID, Name, Phone, ApartmentID

Each apartment can have more than one tenant, but each tenant can be in only one apartment.

There is also an API that returns the current list of apartments, and a list of tenants that are in that apartment as a JSON list:

Apartments : [
    { 
        Name: 'Sherwood', 
        Address: '21 Woody Lane' ,  
        Tenants : [
            { Name: 'Robin', Phone: '111-1111' },
            { Name: 'Hood', Phone: '222-2222' } 
        ]
    }, 

    ...


]

I need to parse the API result into 2 related objects, apartment and tenant, and insert them into the database. There are a lot of entries returned from the API, about 500,000. In order to quicken things up, I have taken ideas from: Fastest Way of Inserting in Entity Framework

However, there is one concept from that post that I am unable to implement in my solution, i.e. deferring SaveChanges(). This is how I have the code (conceptual) currently:

Foreach apartment
    Insert apartment into DB
    SaveChanges()
    Get ID of inserted apartment
    Foreach tenant in apartment
         Insert tenant into DB, into apartment ID

The reason for the above structure is, when inserting a tenant row, I need the apartment ID that that tenant is living in, and therefore I need to have inserted (and saved changes to get the ID of) the apartment prior to inserting the tenant.

This is leading to SaveChanges() being called many times and hurting performance. How can the above solution be optimized?

score 1 · Answer 1 · answered Nov 06 '17 at 10:08

You can assign to navigation properties - EF will handle it on Save, like this:

Foreach apartment
  dbContext.Appartments.Add(apartment);
  foreach tenant
    tenant.Appartment = apartment;
    dbContext.Tenants.Add(tenant);
  endforeach tenant
endforeach apartment

SaveChanges();

score 0 · Answer 2 · answered Nov 06 '17 at 14:32

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 500,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 Bulk Operations
context.BulkInsert(customers, options => {
   options => options.IncludeGraph = true;
});
context.BulkMerge(customers, options => {
   options.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

How to do fast bulk insert through EF on related SQL tables?

2 Answers2