EF Core 6 with Oracle sequence

Posts   
 
    
jovball
User
Posts: 440
Joined: 23-Jan-2005
# Posted on: 29-Jun-2023 22:26:40   

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"));
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 30-Jun-2023 10:27:28   

The documentation of EF Core suggests 'HasDefaultValueSql' (https://learn.microsoft.com/en-us/ef/core/modeling/sequences) and how we generate it does work but looking at our tests, only with fields which have been marked as 'identity' or better, have a sequence defined by Oracle itself. This results in a default value of "schema"."sequence".nextval. The driver will see the table field as an identity field in this case and oracle will pick the next value for the sequence using the default value expression.

I think that's the best workaround you could use, not the 2 you mentioned. Not sure if this is feasible tho in your situation as it does require altering table definitions, which might not be what your DBA wants. However Oracle's 'identity' system basically works this way under the hood: a database generated sequence, assigned to the default value expression of the field.

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 440
Joined: 23-Jan-2005
# Posted on: 30-Jun-2023 12:32:08   

I'll have to see if SQL Server has the same behavior. Changing to identity fields is not an option for us and even if it was we'd have to touch several thousand tables. I expect we'll go with the custom template approach. It's relatively easy to implement compared to anything else because we can make the change in one place and all of the tables using this will work.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 30-Jun-2023 13:07:38   

SQL Server sequences seem to work fine with this setup. To be honest, it's more of a stop-gap what we implemented for Oracle here, as it depends on Oracle if they pick this up for their EF Core driver or not, and as the docs suggest this is the way to go, we decided to do it this way to be in compliance with the EF Core docs. EF Core and Oracle is a rough ride... disappointed

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 440
Joined: 23-Jan-2005
# Posted on: 30-Jun-2023 14:05:26   

Rough ride indeed. Starting with Oracle and then putting EF on top of it. At least EF Core is better than the original EF even though it's got a lot of room to improve before it matches your framework.

jovball
User
Posts: 440
Joined: 23-Jan-2005
# Posted on: 30-Jun-2023 20:28:38   

I'm in the process of customizing the code templates in various ways but this is how I'm modifying the code generation for this situation.

C:\Program Files (x86)\Solutions Design\LLBLGen Pro v5.10\Frameworks\Entity Framework\Templates\VCore\C#\modelBuilder.lpt

I am replacing the else block starting on Line 479. The inner if statement for the ODP.NET is the change, the statement in the inner else was the stock code generation.

else
{
    // Oracle ODP.NET doesn't necessarily work with the HasDefaultValueSql call so use a custom class instead
    if(executingGenerator.DriverID=="A8034CB0-319D-4cdd-BC7D-1E7CFDBA3B74") 
    {
        var sequenceSchema = fieldMapping.SequenceToUse.ContainingSchema.SchemaOwner;
        var sequence = fieldMapping.SequenceToUse.Name;             
        toReturn.Add(string.Format(".ValueGeneratedOnAdd().HasValueGenerator((_, __) => new SequenceValueGenerator(\"{0}\", \"{1}\"))", sequence, sequenceSchema));
    }
    else
    {
        // emit NEXTVAL call for sequence as default value. 
        toReturn.Add(string.Format(".HasDefaultValueSql(\"{0}\")", SD_EF_GeneralUtils.ProduceNextValCallForSequence(_executingGenerator.DriverID, fieldMapping.SequenceToUse)));
    }
}
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 01-Jul-2023 09:28:07   

I think it's better to generate a derived class for the modelbuilder class. I've tested this locally and you can call the Has...() methods on a property multiple times, it'll simply override what was there. This can be done using a custom template that generates 2 additional classes and overrides for the map methods we generate where sequences are generated (so basically the same logic as you use now to generate the call to the HasValueGenerator.) This has the advantage that you can leave the existing templates alone.

Here's an example of what the generated code looks like:

You have to use your derived modelbuilder class so you have to create your derived datacontext class:

public partial class EFCore5OracleDataContextDerived : EFCore5OracleDataContext
{
    public EFCore5OracleDataContextDerived(DbContextOptions<EFCore5OracleDataContext> options) : base(options) {}


    public EFCore5OracleDataContextDerived(string connectionString) : base(connectionString)
    {
    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        new EFCore5OracleModelBuilderDerived().BuildModel(modelBuilder);
    }
}

then in your derived model builder class:

public partial class EFCore5OracleModelBuilderDerived : EFCore5OracleModelBuilder
{
    protected override void MapIdentitytest(EntityTypeBuilder<Identitytest> config)
    {
        base.MapIdentitytest(config);
        config.Property(t => t.Id).HasDefaultValueSql("NEXTVAL FOR \"SCOTT\".\"TESTSEQUENCE\" FROM DUAL");
    }
}

This results in the table now using testsequence. So this shows it works ok. Instead of the HasDefaultValueSql call in your derived class you of course emit a HasValueGenerator() call like you do in your template adjustment.

Frans Bouma | Lead developer LLBLGen Pro