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();

This is not really answering your question, but you could also put an attribute on your entity's ID property (like this: `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]`) which will cause the primary key to be generated automatically. Then you can get the ID that was assigned to the new row by getting the value of your entity's ID property after you call `SaveChanges()`. — Daniel Gabriel, Aug 15 '13 at 19:56
I can insert a new row using by adding 1 to the current max primary key but it doesn't also increase the SEQUENCE value so even though the current code works, if someone inserts a new row using the old .NET webform website, it will break because the SEQUENCE number is out of sync. — iCanObjSeeSharp, Aug 15 '13 at 21:52

score 5 · Answer 1 · edited May 23 '17 at 11:53

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.

Create the sequence (you've already done this) on the identity column.
Create a trigger to automatically call the sequence on insert. http://www.oracle-base.com/articles/misc/autonumber-and-identity.php
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

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.

score 5 · Answer 2 · answered Aug 20 '18 at 02:41

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.

score 1 · Answer 3 · answered Nov 05 '22 at 16:38

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.

score 0 · Answer 4 · edited May 13 '22 at 01:01

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;
   }

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 13 '22 at 14:34

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

4 Answers4