How to insert identity value in Oracle using Entity Framework using a sequence

Question

In an Oracle database, that has an ID column defined as a number:

...and a corresponding sequence for the table...:

How do I make sure the ID column gets the next value in the sequence?

using (var db = new MyOracleDb()){
    var user= new User(){ first_name = 'Abe', last_name = 'Lincoln'};
    //Do something here with the sequence and set the ID?
    db.User.Add(user);
    db.SaveChanges();
}

I am using the latest Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.EntityFramework + EF6x.

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

5

This is not an EF issue, as there is no auto increment in oracle. You will have to either get the sequence value manually, or create a trigger to set it for you.

Update

In order to get the sequence value you have two options - either create a stored procedure, that returns the value - or create a .Net function ( doesn't really have to be in a function, it's just simpler) that calls raw SQL like this:

Database.SqlQuery<int>("SELECT SEQ_SOMESEQ.NEXTVAL FROM dual");

I personally had many issues with oracle functions and EF, so I would go with the raw sql.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 02 '16 at 14:54

gilmishal

1,884
1
22
37

I figured as much, but how do I retrieve the next value in the sequence using EF? Modifying the table (or adding triggers) is not an option in this case. – Kjensen Nov 02 '16 at 15:05
Excellent, I got the value out now. :) – Kjensen Nov 02 '16 at 15:18

score 3 · Answer 2 · answered Mar 19 '20 at 09:38

I've created an interface:

public interface IHasSequencer
{
    decimal Id { get; set; }
    string GetSequenser();
}

Then implemented it in my model:

[Table("CLN_CLIENTS")]
public class ClnClient : IHasSequencer
{
    public string GetSequenser()
    {
        return "SEQ_CLN_CLIENTS";
    }

    [Key, Column("ID")]
    public decimal Id { get; set; }

    [Column("FIRSTNAME")]
    public string FirstName { get; set; }

    [Column("LASTNAME")]
    public string LastName { get; set; }

    [Column("MIDDLENAME")]
    public string MiidleName { get; set; }
}

And added some MAGIC overriding SaveChanges() method in my DbContext:

 public override int SaveChanges()
    {
        var addedEntries = ChangeTracker.Entries().Where(e => e.State == EntityState.Added);

        foreach (DbEntityEntry entry in addedEntries)
        {
            if (entry.Entity is IHasSequencer)
            {
                var sequencerEntity = entry.Entity as IHasSequencer;
                var nextSeqVal = Database.SqlQuery<Int64>("SELECT " + sequencerEntity.GetSequenser() + ".NEXTVAL FROM DUAL").First();
                sequencerEntity.Id = nextSeqVal;
            }
        }
        return base.SaveChanges();
    }

That's it! Now models that need sequenceer ID value must implement small interface.

Awesome solution! I've changed a little bit the interface for making it possible to update tables where the PK is not called "Id". I added a method on the interface called "SetIdFromSequencer" and overridden this method on my entity updating the PK. On SaveChanges I changed `sequencerEntity.Id = ...` to `sequencerEntity.SetIdFromSequencer(nextSeqVal);`. — Rick Wolff, Dec 23 '20 at 22:33

score 0 · Answer 3 · answered Dec 05 '18 at 20:22

Just FYI, With Entity Framework I had issues with unique constraints. For future readers if you're still having unique constraint issues. Please make sure you change the property "StoreGeneratedPattern" to your requirement.

For me I was adding MAX(ID) + 1 and had StoreGeneratedPattern set as Identity which started to work fine but after sometime break the whole program by throwing the same unique constraint issue, which when I checked database was definitely not the case. It was something that most developers didn't pay attention to.

I've changed it to "None" So I have control over it to what to add rather than entity framework breaking it after every few days. Entity Framework Property Picture

score 0 · Answer 4 · edited Feb 07 '19 at 06:36

0

If you create the trigger on Oracle's side you don't have to worry about it on your code. It works as if it did have an Identity.

edited Feb 07 '19 at 06:36

Bhargav Rao

50,140
28
121
140

answered Feb 07 '19 at 06:15

Jules Shred

1

How to insert identity value in Oracle using Entity Framework using a sequence

4 Answers4

Update

Linked