How can an Oracle database's identity be generated automatically using Entity Framework?

.net c# entity-framework ora-00001 oracle

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!

1
12
12/8/2011 4:05:17 PM

Accepted Answer

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;
16
6/7/2012 11:50:37 AM

Popular Answer

Oracle 12c has resolved it

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow