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();
}
}