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?

score 8 · Accepted Answer · answered Oct 22 '18 at 10:01

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.

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! — Vrankela, Feb 05 '20 at 08:14

score 0 · Answer 2 · answered Oct 17 '18 at 18:42

0

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?

answered Oct 17 '18 at 18:42

Devart

119,203
23
166
186

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? – Felix Oct 17 '18 at 22:42
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: https://www.devart.com/dotconnect/oracle/docs/?DBScriptGeneration.html. – Devart Oct 18 '18 at 14:57
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. – Felix Oct 21 '18 at 11:44
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. – Devart Oct 23 '18 at 13:48

Alexander · Answer 3 · 2020-05-23T21:47:07.313

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.

From what I remember, I tried something similar, without success. It makes no sense to me. Maybe try to use trigger with sequence? — Felix, May 24 '20 at 23:10

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

3 Answers3