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