Start Debugging
EN
2026-09-21 ef-coreef-core-10oracleprimary-keys Edit on GitHub

How to use an Oracle sequence to generate primary keys in EF Core

Map an EF Core key to an Oracle sequence with UseSequence in Oracle.EntityFrameworkCore 10: the DDL and INSERT SQL the provider really emits, how to point at an existing sequence or a legacy trigger, why quoting and HiLo trip people up, and how to move an identity column onto a sequence without ORA-00001.

By Marius Bughiu · 9 min read ·

Short answer: with Oracle.EntityFrameworkCore 10.x, declare the sequence and point the key at it with the provider’s own UseSequence: modelBuilder.HasSequence<int>("ORDER_SEQ").StartsAt(1000); then modelBuilder.Entity<Order>().Property(o => o.Id).UseSequence("ORDER_SEQ");. The provider turns that into a column default, "Id" NUMBER(10) DEFAULT ("ORDER_SEQ".NEXTVAL), leaves the key out of the INSERT, and reads the value back with RETURNING "Id" INTO. Do not reach for ValueGeneratedOnAdd() alone (that gives you an identity column, not your sequence), spell the sequence name in the exact case Oracle stores it (usually upper case), and skip UseHiLo on int/long keys, which Oracle’s own README lists as unsupported.

Everything below was checked against Oracle.EntityFrameworkCore 10.23.26301 (published 2026-09-08, assembly 10.0.23.1) on EF Core 10 and the .NET 10 SDK 10.0.302. There is no Oracle Database on the machine I wrote this on, so the SQL shown is what the provider generates, captured offline: GenerateCreateScript() for DDL, IMigrationsSqlGenerator for migrations, and a command interceptor that grabs the SaveChanges command text before it would hit the wire. No timings or live round-trips are claimed.

Why Oracle keys end up on sequences

Oracle had sequences long before it had identity columns (those arrived in 12c), so most Oracle schemas you inherit use one sequence per table, usually <TABLE>_SEQ, and often a BEFORE INSERT trigger that copies NEXTVAL into the key. Even on new schemas, teams pick a sequence over identity because a named sequence can be shared by several tables, can be read by other applications with SELECT ORDER_SEQ.NEXTVAL FROM DUAL, and accepts explicit key values without any special mode.

EF Core’s Oracle provider supports all of this, but its defaults point somewhere else. Out of the box, an int key called Id gets the IdentityColumn strategy:

-- Oracle.EntityFrameworkCore 10.23.26301, default convention for an int key
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,

That is an identity column backed by a hidden system sequence (ISEQ$$_...), not one you named. If your DBA gave you ORDER_SEQ, or your tables already have triggers, you have to say so in the model.

The API surface in the 10.x provider

The Oracle provider ships its own value-generation strategy enum, separate from SQL Server’s. Reflecting over the 10.23.26301 assembly gives:

OracleValueGenerationStrategy: None, SequenceHiLo, IdentityColumn, Sequence
OracleSQLCompatibility:        DatabaseVersion19, DatabaseVersion21, DatabaseVersion23

and these entry points for sequences:

Note that the compatibility enum starts at 19. Older provider versions accepted UseOracleSQLCompatibility("11"), which made the provider create a sequence plus a trigger for every generated key. That mode is gone in the 10.x provider: setting DatabaseVersion19 still produces the same GENERATED BY DEFAULT ON NULL AS IDENTITY column as 23. If you want sequences, you ask for them per property.

Step by step: map a key to a named sequence

  1. Install the provider that matches your EF Core major. For EF Core 10 that is Oracle.EntityFrameworkCore 10.23.x; the nuspec pins Microsoft.EntityFrameworkCore.Relational to [10.0.0, 11.0.0), and there is no EF Core 11 build yet.
  2. Declare the sequence with HasSequence so migrations own its start value, increment, and bounds.
  3. Call UseSequence on the key with the same name (and schema, if any).
  4. Add a migration and read the generated SQL before applying it.
// .NET 10, EF Core 10, Oracle.EntityFrameworkCore 10.23.26301
using Microsoft.EntityFrameworkCore;

public class Order
{
    public int Id { get; set; }
    public string Customer { get; set; } = "";
}

public class ShopContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseOracle(
            "User Id=app;Password=...;Data Source=dbhost:1521/FREEPDB1",
            o => o.UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion23));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("ORDER_SEQ")
            .StartsAt(1000)
            .IncrementsBy(1);

        modelBuilder.Entity<Order>()
            .Property(o => o.Id)
            .UseSequence("ORDER_SEQ");
    }
}

The DDL the provider generates for that model:

-- Oracle.EntityFrameworkCore 10.23.26301, UseSequence("ORDER_SEQ")
CREATE SEQUENCE "ORDER_SEQ" START WITH 1000 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE
"Orders" (
    "Id" NUMBER(10) DEFAULT ("ORDER_SEQ".NEXTVAL) NOT NULL,
    "Customer" NVARCHAR2(2000) NOT NULL,
    CONSTRAINT "PK_Orders" PRIMARY KEY ("Id")
)';
END;

The sequence becomes a plain column default, which Oracle allows from 12c on. That matters: anything else that inserts into the table without supplying Id (a SQL*Plus script, an ETL job, another service) gets a key from the same sequence, not just EF.

If you skip HasSequence and only call UseSequence("ORDER_SEQ"), the provider adds the sequence to the model for you with START WITH 1 INCREMENT BY 1. That is fine for a fresh schema, but declaring it explicitly is the only place to set StartsAt, HasMax, or IsCyclic.

What SaveChanges actually sends

With the key mapped to the sequence, db.Orders.Add(new Order { Customer = "ACME" }) leaves Id at 0 and marks it as a temporary value. SaveChanges then sends one anonymous PL/SQL block:

-- captured from SaveChanges, Oracle.EntityFrameworkCore 10.23.26301
DECLARE
TYPE "rOrders_0" IS RECORD
(
"Id" NUMBER(10)
);
TYPE "tOrders_0" IS TABLE OF "rOrders_0";
"lOrders_0" "tOrders_0";
BEGIN
"lOrders_0" := "tOrders_0"();
"lOrders_0".extend(1);
INSERT INTO "Orders" ("Customer")
VALUES (:p0)
RETURNING "Id" INTO "lOrders_0"(1)."Id";
OPEN :cur0 FOR SELECT "lOrders_0"(1)."Id" FROM DUAL;
END;
-- params: :p0=ACME (Input), :cur0 (Output, ref cursor)

The key column is not in the column list, Oracle fills it from the default, and RETURNING ... INTO hands the generated value back through a ref cursor so EF can write it into order.Id. It is one round-trip per SaveChanges batch, and the same shape as the identity-column case. From EF’s point of view, “identity” and “sequence default” are both “the database generates it on insert”; the difference lives entirely in the DDL.

Now set the key yourself (new Order { Id = 500, Customer = "ACME" }):

-- captured from SaveChanges when Id is set explicitly
BEGIN
INSERT INTO "Orders" ("Id", "Customer")
VALUES (:p0, :p1);
END;
-- params: :p0=500, :p1=ACME

No RETURNING, no special mode. EF sends a non-default key value as-is, and a column default simply does not fire when a value is supplied. Keep in mind that the sequence does not notice either: ORDER_SEQ will still hand out 500 when it gets there, and that insert fails on the primary key. The provider’s identity columns (BY DEFAULT ON NULL) behave the same way. The practical difference is that a named sequence is easy to reason about and move past imported data with one ALTER SEQUENCE, and other code can call NEXTVAL to reserve a key before inserting.

Pointing at a sequence that already exists

For an existing schema you usually do not want EF to create the sequence, you want it to use the one that is there. Three things to get right.

Case. Oracle upper-cases unquoted identifiers, so CREATE SEQUENCE order_seq creates ORDER_SEQ. The EF provider always quotes. UseSequence("order_seq") produces:

CREATE SEQUENCE "order_seq" START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE
"Id" NUMBER(10) DEFAULT ("order_seq".NEXTVAL) NOT NULL,

"order_seq" and ORDER_SEQ are two different objects. Against an existing database, that default fails with ORA-02289: sequence does not exist. Use the name exactly as SELECT sequence_name FROM user_sequences prints it.

Schema. If the sequence lives in another schema, pass it to both calls:

// .NET 10, EF Core 10, Oracle.EntityFrameworkCore 10.23.26301
modelBuilder.HasSequence<long>("ORDER_SEQ", "SALES")
    .StartsAt(1000)
    .HasMax(99999999);

modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .UseSequence("ORDER_SEQ", "SALES");

The generated script first runs a PL/SQL check that raises ORA-01435 if the SALES user does not exist, then creates "SALES"."ORDER_SEQ" and a column default of "SALES"."ORDER_SEQ".NEXTVAL. Your application user needs SELECT on that sequence, or inserts fail at runtime even though the migration succeeded under a more privileged account.

Migrations. EF has no ExcludeFromMigrations for sequences. If the sequence already exists, delete the CreateSequence call from the first migration after it is scaffolded (the model snapshot still records it, which is what you want), or baseline the database with an empty initial migration.

Legacy tables filled by a trigger

Plenty of Oracle schemas have no column default at all. Instead, a trigger does the work:

CREATE OR REPLACE TRIGGER ORDERS_BI
BEFORE INSERT ON "Orders" FOR EACH ROW
BEGIN
  :NEW."Id" := ORDER_SEQ.NEXTVAL;
END;

You can map this without touching the database: tell EF the value is generated on add, and switch off the identity convention so a future migration does not try to add GENERATED ... AS IDENTITY to the column.

// .NET 10, EF Core 10, Oracle.EntityFrameworkCore 10.23.26301
using Oracle.EntityFrameworkCore.Metadata;

modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .ValueGeneratedOnAdd()
    .Metadata.SetValueGenerationStrategy(OracleValueGenerationStrategy.None);

With that mapping, the insert EF sends is byte-for-byte the same INSERT ... RETURNING "Id" INTO block shown above. The trigger runs before the row is written, and RETURNING reads the final row, so EF gets the trigger’s value.

Two traps with triggers:

Long term, moving the trigger’s logic into a column default and mapping it with UseSequence is the cleaner shape: one mechanism, visible in the DDL, understood by migrations.

UseKeySequences for a whole model

If every table should get its own sequence, UseKeySequences() applies the Sequence strategy to every generated key:

// .NET 10, EF Core 10, Oracle.EntityFrameworkCore 10.23.26301
protected override void OnModelCreating(ModelBuilder modelBuilder) =>
    modelBuilder.UseKeySequences();

For Order it creates "OrderSequence" and DEFAULT ("OrderSequence".NEXTVAL). The name is the entity name plus the suffix, and it keeps its mixed case because the provider quotes it. Anyone writing raw SQL now has to type "OrderSequence".NEXTVAL with the quotes. If your naming standard is ORDERS_SEQ, map the sequences per property instead, or combine this with a naming convention like the ones in custom naming conventions for keys and indexes.

Why not UseHiLo

The provider accepts UseHiLo("ORDER_HILO") on an int key and does what hi/lo does: it creates "ORDER_HILO" START WITH 1 INCREMENT BY 10, no column default, and on the first Add runs SELECT "ORDER_HILO".NEXTVAL FROM DUAL synchronously to reserve a block. In my capture, three adds got ids 1, 2, 3 before SaveChanges, and the insert sent all three keys explicitly.

But the 10.23.26301 README says, under Sequences, that the HiLo extension methods are not supported “except for columns with Char, UInt, ULong, and UByte data types”. Building your key strategy on something the vendor documents as unsupported for int and long is a bad trade, and hi/lo has two other costs on Oracle: other writers that insert without the EF client do not know about the block scheme, and the extra NEXTVAL query happens inside Add, which is a synchronous database call even from async code. If you need keys before SaveChanges, generate a GUID or a ULID client-side instead.

Moving an identity column onto a sequence

This is the migration most teams actually need: the table was created by an earlier EF version with the default identity, and now it should use ORDER_SEQ. Change the mapping, add a migration, and the provider generates:

-- IMigrationsSqlGenerator output, identity -> UseSequence("ORDER_SEQ")
CREATE SEQUENCE "ORDER_SEQ" START WITH 1000 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE

DECLARE
   v_Count INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_Count
  FROM ALL_TAB_IDENTITY_COLS T
  WHERE T.TABLE_NAME = N'Orders'
  AND T.COLUMN_NAME = 'Id';
  IF v_Count > 0 THEN
    EXECUTE IMMEDIATE 'ALTER  TABLE "Orders" MODIFY "Id" DROP IDENTITY';
  END IF;
END;

-- followed by a block that runs:
-- ALTER TABLE "Orders" MODIFY "Id" DEFAULT ("ORDER_SEQ".NEXTVAL)

It drops the identity and adds the default without rebuilding the table, which is what you want. What it cannot know is how many rows are already there. StartsAt(1000) on a table whose MAX("Id") is 48210 means the first insert after deploy fails with ORA-00001: unique constraint (PK_Orders) violated, and so do the next 47210. Before generating the migration, query the current maximum and set StartsAt comfortably above it, or add a migrationBuilder.Sql(...) step after the CreateSequence that moves the sequence past the data.

Changing StartsAt later produces a RestartSequenceOperation, which the provider emits as:

ALTER SEQUENCE "ORDER_SEQ" RESTART START WITH 5000

ALTER SEQUENCE ... RESTART is documented from Oracle 19c on. The provider README still carries an older line saying “A sequence cannot be restarted”, so review that statement before relying on it in a production migration, especially if you run it through a migrations bundle where nobody reads the SQL at deploy time.

Gaps, caching, and RAC

A sequence never gives values back. Oracle’s CREATE SEQUENCE reference is explicit: the default is CACHE 20, cached values are lost when the instance fails, and numbers used in a transaction that rolls back are skipped. The provider emits CREATE SEQUENCE without a CACHE clause, so you get that default. Gaps are normal and cheap; do not add NOCACHE to “fix” them, it serialises every insert on a data dictionary update.

On RAC, each instance caches its own range, so keys from two nodes interleave out of order. Oracle notes that ordering “is usually not important for sequences used to generate primary keys”. If you need gap-free, human-facing numbers (invoice numbers with legal requirements), that is a different problem: a sequence is the wrong tool for it on any database.

Sources

Comments

Sign in with GitHub to comment. Reactions and replies thread back to the comments repo.

< Back