In Oracle there is no support for Identity column which we have in SQL. You have to use Sequence to achieve the same functionality in Oracle.

When we develop the MVC application with Entity Framework which is multi DB supported (Both SQL and Oracle), We have to really think about this issue.

One solution is to use Sequence in both SQL (new feature of SQL 2012) and Oracle with Insert Trigger and use StoredGeneratedPattern attribute for Identity column in DBML file. StoredGeneratedPattern has known issue with VS2010 (http://blogs.devart.com/dotconnect/entity-framework-tips-and-tricks-part-4.html#fourth) which is resolved in VS2010 SP1.

Second we can write DB function which returns NEXT value from sequence.

select NEXT VALUE FOR  dbo.Student_Sequence

This way we can deal with sequences using entity framework.

Reference Links

1) http://shanthuk.com/2012/08/26/entity-framework-oracle-indentitysequence-for-inserts/

2) http://www.ladislavmrnka.com/2011/03/the-bug-in-storegeneratedpattern-fixed-in-vs-2010-sp1/




This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s