This is really a question about Oracle and EF Core but the answer might also include LLBLGen templates. My goal is to understand if this a known Oracle/EF Core 6 problem or if the call to NextVal is using incorrect syntax.
We have two known solutions/workarounds for now. One is at the database level and the other would require a change in the LLBLGen code templates for persistence generated code. Both of the workarounds will allow child entities to be inserted during the same save operation without knowing the actual value of the sequence FK value and both of them will return the entity with the sequence value afterwards.
The situation is that the generated code for EF 6 Core creates a model mapping line like this.
config.Property(t => t.Id).HasColumnName("MY_ID").HasDefaultValueSql("NEXTVAL FOR \"MY_SCHEMA\".\"MY_SEQ\" FROM DUAL");
At runtime, that does not work and an insert will fail because the PK column doesn't take null values.
Just in case the generated code was not quite correct for Oracle, I tried these variations with the same failure.
.HasDefaultValueSql("SELECT \"MY_SCHEMA\".\"MY_SEQ\".NEXTVAL FROM DUAL");
HasDefaultValueSql("\"MY_SCHEMA\".\"MY_SEQ\".NEXTVAL")
Known workaround 1 - Database trigger
The database level is just creating a before insert trigger on the table and if the PK column value is null, grab the nextval and use it for the insert.
TRIGGER MY_SCHEMA.MY_TABLE_TRIGGER
BEFORE INSERT ON MY_TABLE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
IF INSERTING THEN
IF (:NEW.MY_ID is null OR :NEW.MY_ID = 0)
THEN
:NEW.MY_ID := MY_TABLE_SEQ.NEXTVAL;
END IF;
END IF;
END;
Known workaround 2 - Helper class and code template change
The code template approach requires one helper class and then a change in the template. We haven't actually made any changes to templates yet, just tested the concept by modifying the generated code.
Helper class - SequenceValueGenerator (hat tip to this post: https://stackoverflow.com/a/60674700)
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Update;
using Microsoft.EntityFrameworkCore.ValueGeneration;
using System;
using System.Globalization;
public class SequenceValueGenerator : ValueGenerator<long>
{
readonly string _schema;
readonly string _sequenceName;
public SequenceValueGenerator(string sequenceName) : this(sequenceName, null) { }
public SequenceValueGenerator(string sequenceName, string schema)
{
_schema = schema;
_sequenceName = sequenceName;
}
public override bool GeneratesTemporaryValues => false;
public override long Next(EntityEntry entry)
{
var context = entry.Context;
var dbSchema = string.IsNullOrEmpty(_schema) ? context.Model.GetDefaultSchema() : _schema;
var sqlGenerator = context.GetService<IUpdateSqlGenerator>();
var sql = sqlGenerator.GenerateNextSequenceValueOperation(_sequenceName, dbSchema);
var rawCommandBuilder = context.GetService<IRawSqlCommandBuilder>();
var command = rawCommandBuilder.Build(sql);
var connection = context.GetService<IRelationalConnection>();
var logger = context.GetService<IRelationalCommandDiagnosticsLogger>();
var parameters = new RelationalCommandParameterObject(connection, null, null, context, logger);
var result = command.ExecuteScalar(parameters);
return Convert.ToInt64(result, CultureInfo.InvariantCulture);
}
}
Revised Template output
config.Property(t => t.Id).HasColumnName("MY_ID").ValueGeneratedOnAdd().HasValueGenerator((_, __) => new SequenceValueGenerator("MY_TABLE_SEQ", "MY_SCHEMA"));
