I have some kind of promblem with BulkInsert on my OracleDB. I need to insert couple of thousand objects so I decided to use EF.BulkInsert.Oracle added by Nuget which is extension of EF6.BulkInsert for Oracle.
private IOracleDbContext _context;//Class property
//method body:
EF6.BulkInsert.ProviderFactory.Register<EF6.BulkInsert.Providers.OracleBulkInsertProvider>("BulkInsertProvider");
using (var context = (OracleDbContext)_context)
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
//Preparing list of objects
var opt = new EF6.BulkInsert.BulkInsertOptions();
opt.Connection = context.Database.Connection;
await context.BulkInsertAsync<ObjectType>(ObjectList,opt);
await context.SaveChangesAsync();
dbContextTransaction.Commit();
stopwatch.Stop();
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
throw ex;
}
}
}
Without adding opt (BulkInsertOptions object) as parameter of BulkInsert it is trying to connect with SQLServer (which don't exist so I get connection failure). After add this BulkOptions with connection I get exception that connection is already part of transaction :/
Traditional way (_context.TableName.Add() ) of course works but It takes unacceptable amount of time.
Any idea what I did wrong here?
I found better way (BulkInsert still do not cooperate). I used Array Binding mentioned here
It reduced insert time from ~6 minutes to ~1-1.5 seconds :D (7770 records)