Unite into Batch Inserts for entities with StoreGeneratedPattern (Oracle Database)

[infinity7117]

Hello,

I see that batching for "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." is not supported. (https://www.devart.com/dotconnect/oracl ... dates.html)

The code generated for such an insert is for this sample table is:
DECLARE
updatedRowid ROWID;
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid;
END;

The SQL for batched inserts could look like this:

DECLARE
updatedRowid1 ROWID;--First in batch
updatedRowid2 ROWID;--Second in batch
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA) --First in batch
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ROWID INTO updatedRowid1;

INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA) --Second in batch
VALUES (:p5, :p6, :p7, :p8, :p9)
RETURNING ROWID INTO updatedRowid2;

OPEN :outParameter FOR
SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid1 --First store generated ID
union all
SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid2; --Second store generated ID
END;

The result is a cursor with the ids for the generated entries. The order is preseved so no confusions are made.

Further, another optimization would seem to be to keep and return the generated values directly, not to re-select them from table by rowid:

DECLARE
generatedId1 number; --First in batch (type of variable is known)
generatedId2 number; --Second in batch (type of variable is known)
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)--First in batch
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ID INTO generatedId1;

INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)--Second in batch
VALUES (:p5, :p6, :p7, :p8, :p9)
RETURNING ID INTO generatedId2;

OPEN :outParameter FOR
SELECT 1, generatedId1 FROM dual --First store generated ID
union all
SELECT 2, generatedId2 FROM dual; --Second store generated ID

END;

So, in conclusion, a single call in Oracle could insert several entries in a table and return the corresponding generated id's.

If this seems like a good idea, please vote on http://devart.uservoice.com/forums/1051 ... th-columns

[Shalex]

You specified a correct PL/SQL block for executing with Oracle. Unfortunately, this approach cannot be implemented for batch updates in scope of the Entity Framework architecture.

If the performance of INSERTs is important for you, we recommend you to avoid using RETURNING clauses by generating true PKs on the client side.

Another way is usage of fake PKs: set StoreGeneratedPattern of your PK column in SSDL to None and assing pseudo values for PK in your code (they will be ignored when inserting records in the database). After SaveChanges(), you should dispose current context (not to use entities in cache with fake IDs), create new DbContext/ObjectContext and retrieve records from database with actual IDs.