Devart.Data.Oracle.EFCore - How to use sequence to set PK column value?

Question

I'm using Entity Framework Core 2.1.4 with Oracle 11 database and Devart.Data.Oracle.EFCore provider. Database first approach.

I want to get from sequence value for ID column (primary key) on inserting without setting this explicitly every time. So, based on similar infos with SQL Server, I did it as following:

Entity

public class Foo
{
    public int Id { get; set; }
    public double Value { get; set; }
}

Mapping (OnModelCreating method)

modelBuilder.HasSequence<int>("SEQ_FOOS", schema: "SCHEMA")
            .StartsAt(1)
            .IncrementsBy(1);

modelBuilder.Entity<Foo>(entity =>
{
    entity.ForOracleToTable("FOOS");
    entity.HasKey(e => e.Id);
    entity.Property(e => e.Id).ForOracleHasColumnName("ID").IsRequired().ForOracleHasDefaultValueSql("SELECT SEQ_FOO.NEXTVAL FROM DUAL");
    entity.Property(e => e.Value).HasColumnName("VALUE");
});

Adding value:

using (var dbContext = new FooDbContext())
{
    var foo = new Foo()
    {
        Value = 5
    };
    dbContext.Foos.Add(foo);
    dbContext.SaveChanges();
}

On SaveChanges:

OracleException: ORA-01400: cannot insert NULL into ("SCHEMA"."FOOS"."ID")

I also logged EF query. As you can see, there is no ID column in insert:

INSERT INTO SCHEMA.FOOS (VALUE)
  VALUES (:p0)

I was trying to use simply SEQ_FOO.NEXTVAL instead of full select or default EF methods (like HasDefaultValueSql) but nothing worked. Even if I type:

ForOracleHasDefaultValueSql("asdasd");

There is no errors with this - only the same exception as above. It seems like EF never call that SQL.

Am I missing something important? Or maybe it's internal Devart problem?

Answer 1

Ok, I have solution. It seems we need to use ValueGenerator. My implementation below.

Mapping

entity.Property(e => e.Id)
      .ForOracleHasColumnName("ID")
      .IsRequired()
      .ValueGeneratedOnAdd()
      .HasValueGenerator((_, __) => new SequenceValueGenerator(_defaultSchema, "SEQ_FOOS"));

SequenceValueGenerator (please note that ValueGenerator is EF Core type)

internal class SequenceValueGenerator : ValueGenerator<int>
{
    private string _schema;
    private string _sequenceName;

    public SequenceValueGenerator(string schema, string sequenceName)
    {
        _schema = schema;
        _sequenceName = sequenceName;
    }

    public override bool GeneratesTemporaryValues => false;

    public override int Next(EntityEntry entry)
    {
        using (var command = entry.Context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = $"SELECT {_schema}.{_sequenceName}.NEXTVAL FROM DUAL";
            entry.Context.Database.OpenConnection();
            using (var reader = command.ExecuteReader())
            {
                reader.Read();
                return reader.GetInt32(0);
            }
        }
    }
}

It seems to work as I needed.