Updated bulk insertion alternative using Entity Framework?

Question

Recently I have been made to update some code changing it all by the add of Entity Framework, until now I have been using bulkcopy without problems but now I am having issues to find a good alternative.

I have to insert a bunch of data on a database, right now I am parsing all the data on a datatable and after that make the bulk insert. the insert it has to be in only one table with 10 columns and the rows that are going to be inserted could vary from 1000 to 5000, maybe more but probably never more that 7000. The table I have prepared with the data "MyTable" and the DB table "MYDB_CONTENT":

 using (Oracle.DataAccess.Client.OracleBulkCopy bulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(dbConnectionString))                       
                    {
                        // The DBtable I'm loading the data to
                        bulkCopy.DestinationTableName = "MYDB_CONTENT";                                            
                        bulkCopy.BatchSize = csvData.Rows.Count;
                        bulkCopy.BulkCopyTimeout = 30;
                        //Load the data to the database
                        bulkCopy.WriteToServer(MyTable);

                        // Close up          
                        bulkCopy.Close();
                        bulkCopy.Dispose();

                        Console.WriteLine("Press Enter to finish.");
                        Console.ReadLine();                            
                    }
                    dbconn2.Close();

I have been reading about and I saw some of the questions related to this are old, but it seems that in this time "Bulk" has not been implemented on EF except for some custom made libraries, I am not sure if I am allowed to use these on my code and others options are not free. also have been trying to change Oracle.DataAccess.client with the Managed DataAccess version but because of the number of insertions it doesn't seem as a good idea.

I have been reading the performance with EF is not very good to big insertions and because this is part of a segmented insertion there can be like 4 to 10 insertions like this one sequentially, plus other stuff going on so it would be great that the time of this wouldn't be very high if possible.

Answer 1

but it seems that in this time "Bulk" has not been implemented on EF except for some custom made libraries

You are right. Only custom library support it.

As you probably already know, OracleBulkCopy is not supported in Managed DataAccess.

You should always use ArrayBinding for inserting over OracleBulkCopy. It's often faster and supported in both libraries (Oracle.DataAccess and Oracle.ManagedDataAccess).

---

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library is NOT FREE.

This library can make your code more efficient by allowing you to save multiples entities at once. All bulk operations are supported:

• BulkSaveChanges
• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

Multiple database providers are supported Including Oracle.

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 Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

Answer 2

I had a similar situation a while ago, I was parsing a CSV file that could contain millions of rows. I read/parsed around 5000 at a time then inserted them into the database and moved onto the next batch.

At the time I was using a different ORM but the principle is still the same. ORMs such as EF6 add a lot of overhead onto your database calls, they need to keep track of relationships and many other things which is why inserting thousands of records is really slow.

I think the way you are currently doing the insert is the most efficient way. If you need speed there is nothing like raw pure ADO.net going direct to SQL server.

One particular tool that really helped me was FastMember written by the excellent Marc Gravell of Stack Overflow! I'm not sure if it supports Oracle but it's worth taking a look.

Edit

I've just remembered about this handy extension which I've used before to add test data to a database with http://entityframework-extensions.net/