How to generate and auto increment Id with EF Core

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?

Answer 1

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;