oracle-databaseentity-frameworkentity-framework-6bulkinsertef-bulkinsert

Using BulkInsert with OracleDB


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?


Solution

  • 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)