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?

Girand

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.

Girand

Thank you for this answer, in my case the auto generated ModelContext didnt even attach the sequence to the Id property so your override helped me a lot! – Maura

Answer 2

Mapping:

private void FooMapping(ModelBuilder modelBuilder)
{
    //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");
    });
}

Code:

    // https://www.devart.com/dotconnect/oracle/docs/?dbmonitor.html
    var monitor = new OracleMonitor() { IsActive = true };

    using (var dbContext = new FooModel())
    {
        dbContext.Database.EnsureDeleted();
        dbContext.Database.EnsureCreated();

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

Check SQL generated in dbMonitor. Is that what you need?

Stereophonic

Hello, thanks for the effort. However that code looks like you want me to delete whole database with hundreds of tables - correct me if I'm wrong - and this is impossible in my case. Is there anything else I can do? – Girand

By default, only the tables and sequences that model objects are mapped to are created/deleted. You can change this behavior via config.DatabaseScript.Schema.DeleteDatabaseBehaviour: devart.com/dotconnect/oracle/docs/?DBScriptGeneration.html. – Stereophonic

Ok, thanks for explanation. I tried to run this (but I also set UseApp property to false because of SocketExceptions), but I got an exception: "Devart.Data.Oracle.OracleException: 'ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater ORA-00722: Feature "SQL identity columns"'" As I mentioned in my question, I'm using Oracle 11 database, so I must use sequence. – Girand

Most likely, you are getting ORA-00406 because your Oracle 11 is upgraded to 12, but its compatibility mode is set to 11. Your approach is good. – Stereophonic

Answer 3

Did not figure this out. I have similar problem on Oracle 18C - I need to fill PK in the table, PK is a NUMBER, not IDENTITY (this is obviously a defect and will be changed later on, but now I have to deal with that since I don't have rights to change DB structure, however I need to prepare CRUD demo). I don't want to use some C# value generator, but instead - DB remedy. So I tried to use the following (but it did not work - the expression is ignored):

b.HasKey(x => x.Id);
b.Property(x => x.Id).HasColumnName("C_LICENCE").IsRequired().ValueGeneratedOnAdd().HasDefaultValueSql("select round(dbms_random.value(100000, 999999)) from dual");

I suspect it's probably because int primary column is never null :) But anyway, I need to somehow force it to be generated via SQL always.

Epigraphy

From what I remember, I tried something similar, without success. It makes no sense to me. Maybe try to use trigger with sequence? – Girand