How to generate and auto increment Id with EF Core Oracle

Question

I use entity framework core 2 Code first to generate data in Oracle 11g, the entity provider is dotConnect, my POCO class is as follow:

public partial class Favoritepage
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int Personnelid { get; set; }
    public int Pageid { get; set; }
}

My DbContext is as follow:

public partial class ModelContext : DbContext
{
    public virtual DbSet<Favoritepage> Favoritepage { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.HasSequence<int>("S_FAVORITEPAGE", "SECURITY")
            .StartsAt(1)
            .IncrementsBy(20);

        modelBuilder.Entity<Favoritepage>(entity =>
        {
            entity.ToTable("FAVORITEPAGE", "SECURITY");

            entity.HasIndex(e => e.Id)
                .HasName("PK_FAVORITEPAGE")
                .IsUnique();

            entity.Property(e => e.Id).HasColumnName("ID").ForOracleHasDefaultValueSql("S_FAVORITEPAGE.NEXTVAL");
        });
    }
}

When I insert entity like this:

static void Main(string[] args)
    {
        using (var dbContxt = new ModelContext())
        {
            var favo = new Favoritepage()
            {
                Pageid = 11253,
                Personnelid = 14313,

            };
            dbContxt.Favoritepage.Add(favo);
            dbContxt.SaveChanges();
            Console.ReadLine();
        }
    }

It does not work, and raise expcetion like this ORA-01400 Cannot insert the value NULL into column 'Id'

What's wrong?

Are you sure you need all those explicit values in your DBContext? It seems redundant with your entity definition and as I remember correctly a lot of that is done automatically. — Toxantron, Jan 03 '18 at 08:49
I know this, i just don't know where the problem is, so I add all these definitions — Nofield, Jan 03 '18 at 09:03
Take a look at the corresponding database table if the `S_FAVORITEPAGE.NEXTVAL` is set as `DEFAULT` for the `ID` column. — Ivan Stoev, Jan 03 '18 at 18:25
@Ivan Stoev Setting `S_FAVORITEPAGE.NEXTVAL` as `DEFAULT` for the `ID` column is unable,maybe my approach is wrong — Nofield, Jan 04 '18 at 02:24

score 3 · Answer 1 · edited Jul 10 '20 at 08:00

3

this way it worked for me.

builder.Property(e => e.Id).HasColumnName("ID")
               .ForOracleUseSequenceHiLo("SEQUENCE_NAME");

edited Jul 10 '20 at 08:00

HK boy

1,398
11
17
25

answered Jul 10 '20 at 01:07

Gerson Henrique Perotoni

31
2

Will this create a trigger? – fsbflavio Apr 15 '21 at 19:56
Starting with `EF core 5` oracle has removed this and expects users to use the `.UseHiLo` method instead – UTF_or_Death Aug 23 '22 at 11:58

score 1 · Answer 2 · answered Jan 30 '19 at 09:11

Because Oracle 11g doesn't support column default value from sequence or function, so the only way is using triggers.

Create Sequence S_XXX;
CREATE OR REPLACE TRIGGER T_XXX
    BEFORE INSERT
    ON XXX
    FOR EACH ROW
BEGIN
    IF (:NEW.ID <= 0) THEN
        SELECT S_XXX.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END T_XXX;

How to generate and auto increment Id with EF Core Oracle

2 Answers2

Linked