How to auto-increment oracle table from Entity Framework code-first approach?

Question

I have the following table where I just added the decorator DatabaseGenerated like so:

public class Reference
{
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public decimal ReferenceId { get; set; }
        public string AddressType { get; set; }
        public string RefferenceType { get; set; }
        public string RefferenceValue { get; set; }
        [ForeignKey("Shipment")]
        public decimal? TrackingNumber { get; set; }
        public Shipment Shipment { get; set; }
}

And my latest up migration that is pushed to the oracle 19c DB is:

public override void Up()
{
    DropPrimaryKey("SALOGSEARCH.References");
    AlterColumn("SALOGSEARCH.References", "ReferenceId", c => c.Decimal(nullable: false, precision: 20, scale: 0, identity: true));
    AddPrimaryKey("SALOGSEARCH.References", "ReferenceId");
}

Yet when I execute my context save after adding:

using (var context = new DbContext())
{
    context.Reference.Add(shipperReference);
    context.SaveChanges();
}

I get an exception

ORA-01400: cannot insert NULL

I assumed that the tag DatabaseGeneratedOption.Identity would generate a sequence in the Oracle database and call it from the default value on next or something. But that is not the case.

Do I have to manually create a trigger and sequence for each column now?

If so then what is the point the of code first if I have to meddle with the database myself.

I'm not sure if this has any effect but I am adjusting the OnModelCreating like so:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("SALOGSEARCH");

    modelBuilder.Properties<string>().Configure(s => s.HasMaxLength(400).HasColumnType("Varchar2"));
    modelBuilder.Properties<decimal>().Configure(s => s.HasPrecision(20, 0).HasColumnType("Number"));
}

Any pointers to solve this from c# side would be much appreciated.

EDIT1: Following this tutorial I've come to realize that the Id of the model class (table) needs to be an int which is number(10, 0) in oracle terms in order to automatically create the desired sequence (as the column default calling nextVal) on the DB side.

Looking at the up migration however, there doesn't seem to be any indication of this sequence creation, so it just leads me to believe that the creation happens somewhere deeper and outside of my field of knowledge.

identity columns are not handled neither by sequences nor triggers. — Roberto Hernandez
– Roberto Hernandez, Commented Aug 4, 2020 at 15:29

marc_s · Accepted Answer · 2020-08-04 16:00:40Z

2

DatabaseGeneratedOption.Identity expects a sequence or an identity column.

Identity columns are supported since version 12c, and are neither referenced by triggers nor sequences. They are handled internally by Oracle. If you get an error

ORA-01400: cannot insert NULL

it is probably because you are making an insert without explicitly naming the columns, or because you are using the wrong IDENTITY type, or because you don't have a sequence.

You have these options for IDENTITY columns

GENERATED ALWAYS: Oracle always generates a value for the identity column. Attempt to insert a value into the identity column will cause an error.
GENERATED BY DEFAULT: Oracle generates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert a NULL value into the identity column.
GENERATED BY DEFAULT ON NULL: Oracle generates a value for the identity column if you provide a NULL value or no value at all.

Example

SQL> create table t ( c1 number generated  by default as identity ( start with 1 increment by 1 ) , c2 number ) ;

Table created.

SQL> insert into t ( c2 ) values ( 1 ) ;

1 row created.

SQL> select * from t ;

        C1         C2
---------- ----------
         1          1

SQL>  insert into t ( c1 , c2 ) values ( null , 1 ) ;
 insert into t ( c1 , c2 ) values ( null , 1 )
                                    *

ERROR at line 1:
ORA-01400: cannot insert NULL into ("MY_SCHEMA"."T"."C1")

However I can make an insert and explicitly refer to the IDENTITY

SQL>  insert into t ( c1, c2 ) values ( 2, 2 ) ;

1 row created.

SQL> select * from t ;

        C1         C2
---------- ----------
         1          1
         2          2

SQL> insert into t ( c2 ) values ( 3 ) ;

1 row created.

SQL> select * from t ;

        C1         C2
---------- ----------
         1          1
         2          2
         2          3

In your case, I would use GENERATED BY DEFAULT ON NULL:

SQL> create table t ( c1 number generated by default on null as identity ( start with 

1 increment by 1 ) , c2 number ) ;

Table created.

SQL>  insert into t values ( null , 1 ) ;

1 row created.

SQL> select * from t ;

        C1         C2
---------- ----------
         1          1

edited Aug 4, 2020 at 16:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 4, 2020 at 15:41

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vrankela Over a year ago

I understand the sql part you wrote down of course. But I cant seem to figure out if i can do the GENERATED BY DEFAULT ON NULL command from c# attributes on my model class or even from the up migration.

Roberto Hernandez Over a year ago

I believe you can't. IDENTITY columns are handled different depending on the database provider. You might try with a trigger/sequence combination, but those will not be created by the DatabaseGeneratedOption in any case

Vrankela Over a year ago

But this isn't some very narrow use case scenario. It's like the most common use case and I can't figure out how to adjust it using code first approach in entity framework.

Collectives™ on Stack Overflow

How to auto-increment oracle table from Entity Framework code-first approach?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related