Problem scaffolding OracleDB using EntityFrameworkCore

User_TKA7X

After scaffolding my Oracle Database I get run time errors that look like this:

The properties x,y,z... are configured to use 'Identity' value generator and are mapped to the same table . Only one column per table can be configured as 'Identity'

I discovered that it can be fixed by removing .ValueGeneratedOnAdd( ) from select columns in the DB Context file.

Is there a way I can scaffold the database without manual edits afterwards?

Manual editing of machine generated code is wasteful, because for example if I have to re-scaffold in the future I will have to do all of this manual work again.

Alex Keh-Oracle

Do the additional columns contain auto-generated Oracle sequences? There can only be one identity column per table, but each table can have multiple columns with sequences. Just trying to understand why ValueGeneratedOnAdd is being generated for the non-identity column in your case.

User_TKA7X

Hi, Alex I am not sure if I know how to answer your question. How can I tell if the columns contain auto-generated Oracle sequences?

What I can say is there are some triggers defined on the table(s) in question. Is that what you mean?

User_TKA7X

Here is the specific error message:

System.InvalidOperationException: 'The properties 'CoilInventory.CoilNo', 'CoilInventory.Weight' are configured to use 'Identity' value generator and are mapped to the same table 'AMROD.COIL_INVENTORIES'. Only one column per table can be configured as 'Identity'. Call 'ValueGeneratedNever' for properties that should not use 'Identity'.'

Here is a trigger related to this table

create or replace TRIGGER "AMROD".coil_productions_insert

BEFORE INSERT ON coil_productions

FOR EACH ROW

DECLARE

  v_seq_no  coil_productions.SEQ_NO%type;

BEGIN

 Select coil_imports_seq_no.nextval

  INTO v_seq_no

FROM DUAL;

 :new.seq_no := v_seq_no;

 :new.coil_no := to_number(to_char(sysdate,'RRRR'),'9999')||:new.coil_no;

END;

So there is some COIL_IMPORT_SEQ_NO, that looks like this:

CREATED 15-JUL-20

LAST_DDL_TIME 15-JUL-20

SEQUENCE_OWNER AMROD

SEQUENCE_NAME COIL_IMPORTS_SEQ_NO

MIN_VALUE 1

MAX_VALUE 999999999999999999999999999

INCREMENT_BY 1

CYCLE_FLAG N

ORDER_FLAG N

CACHE_SIZE 0

LAST_NUMBER 718601

SCALE_FLAG N

EXTEND_FLAG N

SESSION_FLAG N

KEEP_VALUE N

DUPLICATED N

SHARDED N

I can't understand why this would cause the scaffolding error. If you have any suggestions, please let me know

Alex Keh-Oracle

Is the Weight column an IDENTITY column and the CoilNo column uses the trigger to set its value for each INSERT? That would be my guess why the error occurs.

As for updating the EF Core model while maintaining customizations, this is a feature request Microsoft wants to add for all EF Core providers. See Issue 831.

User_TKA7X

Hi Alex, this project has migrated from a very old version of Oracle DB (maybe version 6 or 7). Hence there are NO IDENTITY columns in the table.

There is one primary key in the table which is COIL_NO.

I am wondering if Oracle.EFCore is trying to guess which column is the identity since they are the only two non-nullable int fields in the table. Since there is more than one such field, EFCore is choking on it at runtime

Alex Keh-Oracle

Wow! Is the DB version you are scaffolding from version 6 or 7? Oracle EF Core supports DB 11.2.0.4 and higher. Earlier DB versions can work, but they have not been tested and customers do find errors in scaffolding with earlier versions.

Scaffolding shouldn't use ValueGeneratedOnAdd just for non-nullable columns. Is there anything else special about the Weight column?

User_TKA7X

Alex we have upgraded to 18C. That is the database we scaffolded from.

You are correct that there are quite a lot of properties scaffolded that have ValueGeneratedOnAdd() applied. But I am only seeing these warnings when there is more than one non-nullable int in the table. I saw the same error with some other tables in the same database. Again the error only applied to fields that are non-nullable, non-identity int fields.

As an experiment I will create a brand new table with no triggers, sequences, etc. I will report back my findings.

Alex Keh-Oracle

If you have a scenario that I can try in house and reproduce the problem, it will be easier for my dev team to diagnose the issue, then fix. It could be a corner case we missed.

User_TKA7X

I tried one more experiment. Using SQLDeveloper, I manually inserted one record into the COIL_INVENTORIES table. I could specify the WEIGHT field to any number and it gets inserted correctly. So my feeling is the ValueGeneratedOnAdd is being incorrectly applied to this field by the scaffolder.

Is there any other interpretation one could make in this case?

Alex Keh-Oracle

It's possible Oracle EF Core is hitting a bug.

Can you provide the DDL of the two columns and we'll try to reproduce the problem?