How to bulk process records with Entity Framework and Oracle

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

How to bulk process records with Entity Framework and Oracle

Post by 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.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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- ... html#Batch.

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

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

Post by 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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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 ... oader.html
2. Array binding -> http://www.devart.com/dotconnect/oracle ... yBind.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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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).

Post Reply