oracle-databaseentity-framework-coredevart

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


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?


Solution

  • 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.