How to get Oracle next sequence value in .NET Entity Framework?

Question

I have a web api post method that inserts a new row in my table in my Oracle database. I'm having trouble with the primary key of the table which is a sequence value. How do I do my_primary_key_seq.nextval in Entity Framework? Currently this code works but it will violate PK unique constrain when a new row is inserted via our old .net webform which uses the sequence next value as the next primary key.

decimal nextPK = context.FORMPPs.OrderByDescending(p => p.PPID).FirstOrDefault().PPID + 1;
item.PPID = nextPK;
context.FORMPPs.Add(item);
int result = context.SaveChanges();

Answer 1

I had this same issue, and resolved it with some help from this site and Oracle. I'm assuming you're using Database First, since you mentioned another legacy app uses the same database.

There are a few things I had to do. Like Daniel Gabriel mentioned, if you allow Oracle to manage the identity, you don't need to ever call the sequence to find out the number, the database takes care of it for you. But getting that to work was a little tricky because you may need to make a bunch of changes to the database.

1. Create the sequence (you've already done this) on the identity column.

2. Create a trigger to automatically call the sequence on insert. http://www.oracle-base.com/articles/misc/autonumber-and-identity.php

3. Alter your Entity Framework model. I was using EF Database First, and found this article that explained I needed to alter the model to set the property of the table's identity column to

   StoreGeneratedPattern="Identity"

Oracle entity in VS entity framework doesnt update the primary key in code

4. But I didn't like the fact that I had to re-add this change every time I refreshed my EF model from the database. Double-click the .edmx file, then locate your table in the database diagram, highlight the identity column in the diagram, and in the properties window, change the StoreGeneratedPattern value to Identity. That should make it persist even when you update your EF model.

Answer 2

I used this as a reference:

https://github.com/dotnet/EntityFramework.Docs/tree/main/samples/core/Querying/RawSQL

Data/OracleSequenceContext.cs

using Microsoft.EntityFrameworkCore;

 
namespace EFQuerying.RawSQL;

public class OracleSequenceContext : DbContext
{
    public DbSet<OracleSequence> dbsetOracleSequences { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
        .Entity<OracleSequence>(
            eb =>
            {
                eb.HasNoKey();                
            });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // don't put this in code.
        optionsBuilder.UseOracle("Data Source=XXXXXXX;User ID=TTTTT;Password=PPPPP;");
    }
}

OracleSequence.cs

using System.Collections.Generic;

namespace EFQuerying.RawSQL;
 
public class OracleSequence
{
    public int SequenceValue { get; set; }
    

}

Usage:

    using (var seqContext = new OracleSequenceContext())
    {
        var seqList = seqContext.dbsetOracleSequences
            .FromSqlRaw("SELECT SEQ_PUBLICATION.NEXTVAL as SequenceValue FROM DUAL")
            .ToList();

        Publication.PublicationId = seqList[0].SequenceValue;
   }

Answer 3

My two cents contribution:

string querySeq = "SELECT SCH.YOUR_SEQUENCE.NEXTVAL FROM DUAL";
var conn = (OracleConnection)dbContext.Database.GetDbConnection();
conn.Open();
var command = new OracleCommand(querySeq, conn);
decimal sequence = (decimal) command.ExecuteScalar();

You can get the database connection from your context and use it with the OracleCommand class in order to execute an SQL query using the ODP.net library directly.

Answer 4

The way I got around this was select a new value from the sequence using a raw SQL query.

i.e.

decimal nextPK = context.Database.SqlQuery<decimal>("SELECT my_primary_key_seq.nextval FROM dual").First();

And then just assign this value to the new object before adding it to the context.