Kjensen Kjensen - 1 month ago 17
C# Question

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

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

enter image description here

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

enter image description here

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.

Answer

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.