Forum Stats

  • 3,816,407 Users
  • 2,259,184 Discussions
  • 7,893,474 Comments

Discussions

Problem scaffolding OracleDB using EntityFrameworkCore

User_TKA7X
User_TKA7X Member Posts: 12 Green Ribbon

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.

Tagged:
«13

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,062 Employee

    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
    User_TKA7X Member Posts: 12 Green Ribbon

    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
    User_TKA7X Member Posts: 12 Green Ribbon

    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
    Alex Keh-Oracle Posts: 3,062 Employee

    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
    User_TKA7X Member Posts: 12 Green Ribbon

    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
    Alex Keh-Oracle Posts: 3,062 Employee

    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
    User_TKA7X Member Posts: 12 Green Ribbon
    edited May 25, 2021 12:56PM

    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
    Alex Keh-Oracle Posts: 3,062 Employee

    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
    User_TKA7X Member Posts: 12 Green Ribbon

    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
    Alex Keh-Oracle Posts: 3,062 Employee

    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?