Entity Framework Beta 3 support for Oracle 11.2

plig

Hello,

I am experimenting with the beta 3 of Entity Framework provider (released yesterday 02.04) and noticed several issues related to Oracle 11.2 support. I am using .NET Core 2.2 on Windows 10, Oracle 11.2 on Linux

- When scaffolding an existing database with a command like:

Scaffold-DbContext "User Id=******;Password=********;Data Source=********;" Oracle.EntityFrameworkCore -context MyContext

the scaffold does not succeed because it tries to execute:

SELECT sys_context('userenv', 'current_schema') as schema, c.table_name, c.column_name, c.column_id, c.data_type, c.char_length, c.data_length, c.data_precision, c.data_scale, c.nullable, c.data_default, c.virtual_column, c.hidden_column, c.user_generated FROM user_tab_cols c INNER JOIN user_tables t  ON t.table_name=c.table_name WHERE t.table_name <> '__EFMigrationsHistory'  ORDER BY c.column_id 

which emits (0x80004005): ORA-00904: "C"."USER_GENERATED": invalid identifier,

because this particular column was introduced in user_tab_cols in Oracle 12c.

Is there a switch to use Oracle 11.2 when scaffolding?

- second problem (after I used a workaround to bypass the first problem) is with an identity column which have a trigger and sequence to increment server side.

I have added , b => b.UseOracleSQLCompatibility("11") in optionsBuilder.UseOracle(...) as suggested in the documentation

When I add an entity to context and then execute context.SaveChanges() it fails every time with a .NET Exception (ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.) no matter whether I added entity.Property(e => e.Id).UseOracleIdentityColumn() or not.

The only way it works properly is if I add entity.Property(e => e.Id).ValueGeneratedNever(); in OnModelCreating(...) in the generated context

However in this case the Id of the property is not populated afterwards.

Most probably both of my problems are connected with the fact that I use Oracle 11.2, but according to the documentation this should be a supported scenario. May be I am missing something obvious here.

P.S: When using beta 2 of the Oracle EF Core provider first problem didn't happen, only the second one

Best regards,

Plamen

Alex Keh-Oracle

The first issue was reported by another customer and has been filed as a bug (29593228). Oracle will fix it by production.

The second issue is not something we've seen before, or at least I haven't. Do you have a simple test case to reproduce? It's curious that .NET is rejecting the data value. Could you be at the limit of the .NET data type for the entity? .NET numeric data types and Oracle NUMBER data types will not have the same value ranges.

Alex Keh-Oracle

The first issue was reported by another customer and has been filed as a bug (29593228). Oracle will fix it by production.

The second issue is not something we've seen before, or at least I haven't. Do you have a simple test case to reproduce? It's curious that .NET is rejecting the data value. Could you be at the limit of the .NET data type for the entity? .NET numeric data types and Oracle NUMBER data types will not have the same value ranges.

LeChuck

Thanks for the post. I ran into the same problem today. Glad that it's already reported.

plig

I have set up a simple repository to illustrate the second problem:

https://github.com/pignatov/OracleEFCore

I inspected the logging of the database provider and found that the cause of the problem is most probably this error:

System.InvalidOperationException: The database generated a null value for non-nullable property 'Id' of entity type 'Ehic'. Ensure value generation configuration in the database matches the configuration in the model.

user582104

How did you work around the first problem?

plig

By overlapping the system table name into your schema and adding the missing column. It is not safe for general use, because it does not populate any data in the column of this view.

create view your_schema_name.user_tab_cols

as

select c.*,

            '' as user_generated

from sys.user_tab_cols c;

Alex Keh-Oracle

@plig

Since you can only reproduce the second issue after working around the first issue, I would like to get a fix for issue #1 first. Then, we'll see if issue #2 still exists. It's possible that #2 is a direct result of missing metadata needed if #1 was set up correctly.

plig

Hello,

problem #1 didn't occur in beta 2 though, only problem #2.

Is there a roadmap when a new beta will be released so problem #2 can be traced?

plig

After some more debugging, there are more hints what might be the issue with problem #2

This is the PL-SQL code, which is generated when the Id column is initialized with UseOracleIdentityColumn()

DECLARETYPE rEHIC_0 IS RECORD("Id" NUMBER(10));TYPE tEHIC_0 IS TABLE OF rEHIC_0;lEHIC_0 tEHIC_0;v_RowCount INTEGER;BEGINlEHIC_0 := tEHIC_0();lEHIC_0.extend(1);INSERT INTO "NHIFEESSI"."EHIC" ("FullName", "IDCardIssueDate", "IDCardNo", "PIN")VALUES (:p0, :p1, :p2, :p3)RETURNING "Id" INTO lEHIC_0(1);OPEN :cur1 FOR SELECT lEHIC_0(1)."Id" FROM DUAL;END;

However it seems that in Oracle 11.2 this line

RETURNING "Id" INTO lEHIC_0(1);

does nothing. No exception but Id is not returned in the id column as intended.

It seems (although not sure) that in Oracle 12 and up the above line populates the Id column properly.

If I change the line above to

RETURNING "Id" INTO lEHIC_0(1)."Id";

(explicitly adding the "Id" name), it seems to do the job.

Of course I cannot test the provider, but the pl-sql code seems to produce more consistent results.

3998755

Hi,

Any word on the first issue?  I'm starting on a new project and I really would want to be able to use the Entityframework core. This is stopping me

Alex Keh-Oracle

Bug 29593228 has been fixed and will be part of the Beta 4.

3998755

Great!  What's the eta of the release?

Alex Keh-Oracle

End of this month is the target Beta 4 date.

plig

Hello,

I can confirm that unfortunately the second problem still exists even with the newly released beta4.

The Id (primary key column) is still not populated after SaveChanges of the context no matter what options I give (UseOracleIdentityColumn or not)

Alex Keh-Oracle

I filed a new bug 29869371 to investigate the index out of range issue.

Alex Keh-Oracle

@plig

We tried running your test case against Oracle DB 11.2.0.4.2, but could not reproduce your error. Upon executing the app, the ID value was successfully inserted into the table.

We also see the line:

RETURNING "Id" INTO lEHIC_0(1);

But it executes successfully.

plig

The record in the table is populated correctly because the Id is generated properly using the trigger in the DB.

The problem is that the Id is not returned to the entity and cannot be read from the C# code.

For instance, now I am using the following code:

            AuditData audit = new AuditData()

            {

                // This is a fix, so I should not put ValueGeneratedNever in the OnModelCreating() method for this propery

                Id = -1,

                TimeAccessed = DateTime.Now,

            };

            _context.AuditData.Add(audit);

           _context.SaveChanges();

            // TODO Temporary fix. Should be: return audit.Id

            return _context.AuditData.Max(i => i.Id);

I have tried all the combinations I could think of and it either throws exception or does not return the id (primary key) in the entity after SaveChanges

Alex Keh-Oracle

@plig

We are not able to reproduce this issue on DB 11.2. We can get the "Id" value after SaveChanges().

                    Ehic ehic = new Ehic()

                    {

                        FullName = "John Smith",

                        IdcardIssueDate = new System.DateTime(2010, 03, 21),

                        IdcardNo = "1234567890",

                        Pin = "0987654321"

                    };

                    context.Ehic.Add(ehic);

                    context.SaveChanges();

                    foreach (Ehic item in context.Ehic)

                    {

                        LogOutput("ID: "+ item.Id);

                    }

Which DB 11.2 version are you using? We are testing with DB 11.2.0.4.190115.

plig

Hello and thanks for the efforts in reproducing the problem. I made a comment in the example code below to better explain the issue:

                    Ehic ehic = new Ehic()

                    {

                        FullName = "John Smith",

                        IdcardIssueDate = new System.DateTime(2010, 03, 21),

                        IdcardNo = "1234567890",

                        Pin = "0987654321"

                    };

                    context.Ehic.Add(ehic);

                    context.SaveChanges();

                    // The problem is observed here (after SaveChanges)

                    // ehic.Id is missing here. The record is saved properly in the database (e.g. id = 5), but the id (primary key) is not returned after SaveChanges and stays empty/ null/ or something else

                    // The expected behavior (at least by me) is that the primary key should be populated after saving the entity

                    // The next lines work perfectly fine because they select again all the rows from the table

                    foreach (Ehic item in context.Ehic)

                    {

                        LogOutput("ID: "+ item.Id);

                    }

Best regards,

Plamen Ignatov

Alex Keh-Oracle

It also works when we try to access the Id immediately after SaveChanges():

Ehic ehic = new Ehic()

                {

                    FullName = "John Smith",

                    IdcardIssueDate = new System.DateTime(2010, 03, 21),

                    IdcardNo = "1234567890",

                    Pin = "0987654321"

                };

                context.Ehic.Add(ehic);

                context.SaveChanges();

                Console.WriteLine(ehic.Id);

tperetzke

Hello Alex,

can anybody already say something about the current status of the bug with the ArgumentOutOfRangeException ("Index was out of range...")?

(https://support.oracle.com/rs?type=bug&id=29869371)

We are highly interested into it as well, and would like to know if this will be fixed in one of the next beta- or production-versions already? We experience the exact same behaviour with the current Oracle.EntityFrameworkCore Beta4 package as like as plig described already. Our ORACLE Database is Version 11.2.0.1.0 (linux/64bit).

If this issue is not solved yet, because you might need further information, I hope that the following description helps you to find the solution:

By enabling a logger we got the generated SQL-Statement out which was fired to the database leading to that Exception and analyzed it.

The following statement was generated for a table called EF_CORE_TEST_ENTITIES having only two columns: ID and TEXT_CONTENT / ID-column would be automatically filled by a sequence through an INSERT-trigger:

DECLARE

    TYPE rEF_CORE_TEST_ENTITIES_0 IS RECORD

    (

        "ID" NUMBER(10)

    );

    TYPE tEF_CORE_TEST_ENTITIES_0 IS TABLE OF rEF_CORE_TEST_ENTITIES_0;

    lEF_CORE_TEST_ENTITIES_0 tEF_CORE_TEST_ENTITIES_0;

    v_RowCount INTEGER;

BEGIN

    lEF_CORE_TEST_ENTITIES_0 := tEF_CORE_TEST_ENTITIES_0();

    lEF_CORE_TEST_ENTITIES_0.extend(1);

   

    INSERT INTO "OUR_SCHEMA_NAME"."EF_CORE_TEST_ENTITIES" ("TEXT_CONTENT")

        VALUES (:p0)

            RETURNING "ID" INTO lEF_CORE_TEST_ENTITIES_0(1);

   

    OPEN :cur1 FOR SELECT lEF_CORE_TEST_ENTITIES_0(1)."ID" FROM DUAL;

END;

Providing now a valid value for ":p0", the cursor ":cur1" itrerates over one row and one column, but always with a NULL value inside.

Changing the line containing the "RETURNIG" keyword to...

          RETURNING "ID" INTO lEF_CORE_TEST_ENTITIES_0(1)."ID";

          (just added the ID-column at the end)

... let the cursor show now the desired newly created value from the sequence.

We think that in ORACLE version 11 it is necessary to mention the exact column there even that only one exists.

Maybe newer versions do not need it explicitly... anyway, we can't test it.

By the way:

We used "UseOracleIdentityColumn()" for the Id-Property in our EntityTypeBuilder, and it made no difference to call "UseOracleSQLCompatibility("11")" in our DbContextOptionsBuilder - we always got the same generated SQL-code as seen above.

By the way 2:

Will it be necessary to always call "UseOracleIdentityColumn()" in our EntityTypeBuilders? Because we ever thought a "ValueGeneratedOnAdd()" on an ID-Property should generally express exactly that already, but without the need of a hard reference to the ORACLE-namespaces already here...

However,

also a big thank you from me for the efforts and progress already made here!

Best regards,

Thomas Peretzke

Alex Keh-Oracle

tperetzke

We haven't made any progress on the bug fix due to an inability to reproduce the issue. I will pass along the details you have provided to the developer working on the bug.

Alex Keh-Oracle

The Oracle .NET team has a fix for Bug 29869371. We plan to add it into the production release. We would like to have a customer try out the fix to verify the fix is working as expected. If you would like to test the fix, please email me alex.keh (at) oracle.com.