How to automatically generate identity for an Orac

Question

I'm using Oracle provider for Entity framework (beta), and I'm facing a problem.

Our tables have Id columns, which are set to be Identity in StoreGeneratedPattern. I thought that EF will automatically do "underlying works", such as create sequences, and get new identity for each record I add to the table. But when I run code to add a new record, such as:

var comment = new Comment
{
    ComplaintId = _currentComplaintId,
    Content = CommentContent.Text,
    CreatedBy = CurrentUser.UserID,
    CreatedDate = DateTime.Now
};

context.Comments.AddObject(comment);
context.SaveChanges();

an Exception still throws, which is

{"ORA-00001: unique constraint (ADMINMGR.CONSTRAINT_COMMENT) violated"}

(CONSTRAINT_COMMENT is the constrain requires that comment identity must be unique.

How do I solve this?

Thank you very much!

Answer 1

I am using Oracle ODP.NET, Managed driver and Entity Framework 6. I created my tables using the code-first approach but wasn't able to add any records due to a null primary key.

The solution was to grant my user both:
'CREATE SEQUENCE' and
'CREATE TRIGGER'
permissions and re-create the schema.

I realized this after using the -verbose flag in the package management console

Answer 2

Oracle 12c has resolved it

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int SomeNumber { get; set; }

Answer 3

Instead of remember all of this SQL, you could easily do by using Mig# like this:

        var schema = new DbSchema(ConnectionString, DbPlatform.Oracle12c);
        schema.Alter(db => db.CreateTable("TableName")
            .WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
            ...);

In this example, the Id column will have the required trigger and sequence generated by Mig# automatically.

Answer 4

Another option would be:

Create a sequence the way Alextansc described. Create a stored procedure that uses MySequence.nextval as it's primary key.

Map 'insert' for this model to your stored procedure and it works!

I've tested this using database first approach.

Using database first mapping to a stored procedure is pretty simple. Go to your edmx file and right click the model you want to map to a stored procedure. Click "stored procedure mappings." The dialog at the bottom of the page gives you three drop down menus for mapping insert, update, and delete to stored procedures.

Answer 5

StoreGeneratedPattern="Identity" simply tells EF that the value will be generated DB-side on insert, and that it shouldn't supply a value in insert statements.

You still need to create a sequence in Oracle:

create sequence ComplaintIdSequence minvalue 1 maxvalue 9999999 start with 1 increment by 1;

and a trigger to make table inserts use it:

create or replace trigger CommplaintIdTrigger  
before insert on comment for each row 
begin 
  if :new.ComplaintId is null then select ComplaintIdSequence.nextval into :new.ComplaintId from dual; 
  endif; 
end;