使用EF5插入数据时遭遇ORA-00001主键重复约束错误

The ORA-00001 error tells you’re trying to insert a duplicate value into the primary key column of the NCORE_TRN_CASH_IN_INFO table (enforced by the PK_NCORE_CASH_IN constraint). Since your code worked previously, this is almost certainly a problem with how you’re generating unique primary key values—especially if you’re relying on manual ID assignment instead of Oracle’s native sequence/trigger system. Here are actionable fixes tailored to EF5 (since you can’t upgrade):

1. Ditch "Max ID + 1" for an Oracle Sequence #

If your code generates IDs using something like Obj_nCoreEntities.NCORE_TRN_CASH_IN_INFO.Max(x => x.ID) +1, this is a recipe for race conditions: multiple concurrent requests can grab the same ID at the same time. Instead, use an Oracle sequence to guarantee unique values every time.

Step 1: Create an Oracle Sequence (if it doesn’t exist) #

Run this SQL in your Oracle database to set up a sequence for your table:

CREATE SEQUENCE SEQ_NCORE_CASH_IN
START WITH 1 -- Replace with your current max table ID + 1
INCREMENT BY 1
NOCACHE; -- Use CACHE for better performance if gaps from rollbacks are acceptable

Step 2: Fetch the Next Sequence Value in Your EF5 Code #

Modify your insert logic to pull the next unique ID directly from the sequence before creating the entity:

using (TransactionScope transactionScope = new TransactionScope()) {
    try {
        NCORE_TRN_CASH_IN_INFO OBJ_NCORE_TRN_CASH_IN_INFO = new NCORE_TRN_CASH_IN_INFO();
        
        // Get the next unique ID from the sequence
        int nextId = Obj_nCoreEntities.Database.SqlQuery<int>("SELECT SEQ_NCORE_CASH_IN.NEXTVAL FROM DUAL").Single();
        OBJ_NCORE_TRN_CASH_IN_INFO.ID = nextId;
        
        // Set other entity properties here...
        
        Obj_nCoreEntities.NCORE_TRN_CASH_IN_INFO.Add(OBJ_NCORE_TRN_CASH_IN_INFO);
        Obj_nCoreEntities.SaveChanges();
        
        transactionScope.Complete();
    } catch (Exception ex) {
        // Handle exceptions as needed
    }
}

2. Use a Database Trigger to Auto-Assign PK Values #

For a more hands-off approach, create a trigger that automatically populates the PK column with the next sequence value when inserting a new row. This way, you don’t need to handle ID generation in your EF code at all.

Step 1: Create the Trigger #

Run this SQL in Oracle to set up the trigger:

CREATE OR REPLACE TRIGGER TRG_NCORE_CASH_IN_PK
BEFORE INSERT ON NCORE_TRN_CASH_IN_INFO
FOR EACH ROW
BEGIN
    -- Only set the ID if it's not explicitly provided (optional)
    IF :NEW.ID IS NULL THEN
        SELECT SEQ_NCORE_CASH_IN.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/

Step 2: Configure EF5 to Recognize Auto-Generated IDs #

Mark your ID property in the NCORE_TRN_CASH_IN_INFO entity class to let EF know it’s generated by the database:

public class NCORE_TRN_CASH_IN_INFO {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    
    // Add other entity properties here...
}

Now you can insert new entities without setting the ID—the trigger will handle assigning a unique value, and EF will automatically retrieve the generated ID after SaveChanges().

3. Fix Sequence-Table Alignment #

If you recently restored the database or modified the sequence, check if the sequence’s current value is out of sync with the highest existing ID in the table. Run these queries to verify:

-- Get the highest ID currently in the table
SELECT MAX(ID) FROM NCORE_TRN_CASH_IN_INFO;

-- Get the current value of the sequence
SELECT SEQ_NCORE_CASH_IN.CURRVAL FROM DUAL;

If the sequence’s current value is lower than the max table ID, reset it to start after the highest existing ID:

ALTER SEQUENCE SEQ_NCORE_CASH_IN
START WITH [MAX_ID_PLUS_1] -- Replace with your max table ID + 1
INCREMENT BY 1;

Quick Reminders for EF5 + Oracle #

• EF5 doesn’t have native support for Oracle sequences, so manual fetching or triggers are your only reliable options.
• Never use Max(ID)+1 for ID generation—it’s not thread-safe and will cause duplicate errors under load.
• Oracle sequences increment even if a transaction rolls back, which is normal (gaps in PK values are acceptable for most use cases).

内容的提问来源于stack exchange，提问作者salma sultana