How to bulk process records with Entity Framework and Oracle

How to bulk process records with Entity Framework and Oracle

Postby cResults » Fri 12 Apr 2013 19:37

We're using dotConnect for Oracle as our Entity Framework data provider for our Oracle data implementation and we are very please with it.

Our app uses EF for both Sql Server and Oracle. We have a few queries that we've needed to convert to store procedures due to performance issues. One specifically, passes an IDataReader via a SqlParameter of SqlDbType.Structured to a sproc that does a number of bulk operations. This sproc handles tens of thousands of records with great speed.

We need a high performance means of accomplishing the same result on our Oracle implementation. Does dotConnect for Oracle or any other Devart product provide a means of handling bulk records from EF in a speedy manor?

Any ideas or recommendations will be appreciated.
cResults
 
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: How to bulk process records with Entity Framework and Oracle

Postby Shalex » Tue 16 Apr 2013 15:52

cResults wrote:Does dotConnect for Oracle or any other Devart product provide a means of handling bulk records from EF in a speedy manor?

Please refer to http://blogs.devart.com/dotconnect/new-features-of-entity-framework-support-in-dotconnect-providers.html#Batch.
Shalex
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44

Re: How to bulk process records with Entity Framework and Oracle

Postby cResults » Tue 16 Apr 2013 16:17

Thank you Shalex.

I read through the post you linked to. I don't think this will work for us. First, the limitation of not being able to use TransactionScope, likely disqualifies this as an option.

Second, if the following means that we can't insert a collection of records and receive back the primary key this won't work.
The following statements cannot be put into batches:
Statements with the RETURNING clause, for example, statements that update or insert entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed.


We need to be able to insert a batch of records, get the primary key from the output and insert records in two child tables. In Sql Server we can do this with a single stored procedure. Can this be done in Oracle?

If I'm wrong about being able to receive the primary key or if there are any other options, please let me know.

Respectfully,

cResults
cResults
 
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: How to bulk process records with Entity Framework and Oracle

Postby Shalex » Thu 18 Apr 2013 10:39

cResults wrote:the limitation of not being able to use TransactionScope, likely disqualifies this as an option.

If you are going to work within TransactionScope, we recommend you to use a fast data insertion of the ADO.NET level:
1. OracleLoader class -> http://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleLoader.html
2. Array binding -> http://www.devart.com/dotconnect/oracle/docs/?ArrayBind.html

cResults wrote:get the primary key from the output and insert records in two child tables.

For this, please use OracleCommand.ExecuteNonQuery() and pass IDs generated in the database back via return or output parameters of OracleCommand.
Shalex
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44

Re: How to bulk process records with Entity Framework and Oracle

Postby Shalex » Mon 18 Jul 2016 08:51

The current EF6 architecture doesn't allow to use Batch Updates with StoreGeneratedPattern=Identity/Computed.

A workaround for the case when the current context instance is used for the batch updates operation only: modify a metadata, used for creating context instance, by removing StoreGeneratedPattern=Identity/Computed from properties. This is easier to do with fluent mapping (this is possible with XML Mapping as well).
Shalex
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle