2

We are trying to insert a large dataset (2+ million records) into our database, using Entity Framework bulk insert.

The extensions is EntityFramework.BulkInsert.Extensions (nuget EntityFramework.BulkInsert-ef6) Version 6.0.2.8.

We are using this the following way:

using (StockEntities context = new StockEntities())
{
    var entities = AutoMapper.Mapper.Map<List<ImportLots>>(lots).ToArray();
    context.BulkInsert(entities, new BulkInsertOptions { TimeOut = 1800 });
    context.SaveChanges();
}

However, after about 1 minute, this times out.

So, in a attempt to fix this, we added a batchsize

using (StockEntities context = new StockEntities())
{
    var entities = AutoMapper.Mapper.Map<List<ImportLots>>(lots).ToArray();
    context.BulkInsert(entities, new BulkInsertOptions { TimeOut = 1800, BatchSize = 100000 });
    context.SaveChanges();
}

However, no dice. It seems like BulkInsertOptions is ignored. Has anyone else encountered this? And what is the best way to fix this?

Edit: added the following just to test, however, still no dice. It still times out after about 1 minute.

context.Database.CommandTimeout = 1800;

Edit: Transformed the code to the following and this seems to work, so it seems BulkInsertOptions are ignored

using (StockEntities context = new StockEntities())
{
    context.Database.CommandTimeout = 1800;
    var entities = AutoMapper.Mapper.Map<List<ImportLots>>(lots).ToArray();
    int batches = entities.Count() / 100000;
    for(int i = 0; i < batches; i++)
    {
        context.BulkInsert(entities.Skip(i * 100000).Take(100000), new BulkInsertOptions { TimeOut = 1800});
        context.SaveChanges();
    }
}
Vincentw
  • 164
  • 3
  • 17

0 Answers0