search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
408 stars 190 forks source link

Oracle.EntityFrameworkCore for NET 6 - how to set next identity value using a sequence #249

Closed abrasat closed 1 year ago

abrasat commented 1 year ago

Is there any code sample available for the Oracle.EntityFrameworkCore library about how to set the next value for the identity? The next identity value should be generated by using a raw SQL call from the NET 6 code (and not by using stored procedures).

alexkeh commented 1 year ago

If you are using a sequence, you can set UseOacleSQLCompatibility=11 and UseIdentityColumn=Enabled.

More info on Oracle EF Core Identity Columns, Sequences, and Triggers

abrasat commented 1 year ago

@alexkeh : Thanks for the link. I used an Oracle Autonomous Database for testing, and my test application used the code-first approach. I just set ValueGeneratedOnAdd() for the identity column in the override of OnModelCreating(), and did not set UseOracleSQLCompatibility and UseIdentityColumn at all.

...
   entity.Property(e => e.Id)
        .HasColumnType("NUMBER")
        .ValueGeneratedOnAdd()
        .HasColumnName("ID");
...

The table was created as expected, with the Identity flag set for the "Id" column. The Id is "automatically" incremented each time as a new record is inserted into the table, it seems to work fine. I just noticed that if some records are deleted, and then new entries are added, the Ids are further incremented, and the Ids which were deleted remain "unused".

alexkeh commented 1 year ago

That's great you found a solution with an Oracle IDENTITY column. That is generally the preferred way for apps to set up an auto-incrementing column nowadays. My proposal was targeted for a non-IDENTITY column since I thought you weren't using them for some reason.

abrasat commented 1 year ago

@alexkeh It is anyway useful to know about all options, as customers oft have legacy systems with already available Oracle database schemas.