Devart Forums

Does dotConnect for oracle supports Sequences in the Entity Framework? How can a SEQ.NEXT_VAL o SEQ.CURRENT_VAL be inserted into a new record?

Create an ON INSERT trigger in your database that will insert the NEXT_VAL into the column value
and then set the StoreGeneratedPattern attribute to "Identity" for the corresponding column in the Store
part of the Entity model.

willfer wrote:Does dotConnect for oracle supports Sequences in the Entity Framework? How can a SEQ.NEXT_VAL o SEQ.CURRENT_VAL be inserted into a new record?

I've just had this exact same issue. I spent two days trying to find out if it can be done - and the conclusion I came to was basically, not really. Apparently you can do it if you edit your model in Devart's Entity Developer. However, their Entity Developer fell over just trying to open my model so I didn't bother with it.

In the end, the simplest solution for me was to simply create a new connection and call a stored proc that returned the next val. Both connections (the stored proc one and the EF one) were both under the same TransactionScope so it's very easy to enlist them in the same transaction.

I wasted a lot of time following useless examples that don't work on the web. Not worth the bother, the next release of EF will fix this anyway.

AndreyR wrote:Create an ON INSERT trigger in your database that will insert the NEXT_VAL into the column value
and then set the StoreGeneratedPattern attribute to "Identity" for the corresponding column in the Store
part of the Entity model.

I followed this and it returns an error situation "Error on executing DbCommand." The inner exception is from Oracle: "ORA-00001: unique constraint (MY.PRIMARY_KEY) violated."

I assume it's because the EF is attempting to insert a value for the identity column. What is the workaround to this? (BTW, how did this *not* make it into the use cases for Oracle? Sequences are pretty obvious, I would think.)

Please make sure that the StoreGeneratedPattern attribute is set to "Identity" for the corresponding column in the storage part of your model (SSDL): http://msdn.microsoft.com/en-us/library/bb738536.aspx.
The "unique constraint violated" error is arised if you are trying to insert a nonunique value. It seems like your sequence returns the values that have been already inserted to the table as primary keys. Please truncate your table and try to fill it using Entity Framework. If it doesn't help, please tell us your version (x.xx.xx), and send us a small test project with your model and the DDL/DML script of your database objects. We will try to reproduce the issue in our environment.

I'd like to auto-populate the PK ID for a table on insert, as has been discussed in this thread.

This code works, but isn't thread-safe.
dist.Distributionid = (from d in DB.Distributions select d.Distributionid).Max() + 1;

Has this been added to version 1.0.31.0 of the Devart.Data.Linq.Web.dll?

Thank you,
MAbraham

In case you are using LINQ to SQL, then this is enough to set the Auto Generated Value property to true and the Auto-Sync property to OnInsert in the model designer for the Autoincrement property.
Please note that your DB part should contain trigger or any other autoincrement enabled depending on the DBMS you are using.

Shalex wrote:Please make sure that the StoreGeneratedPattern attribute is set to "Identity" for the corresponding column in the storage part of your model (SSDL): http://msdn.microsoft.com/en-us/library/bb738536.aspx.

That tripped me up, because setting it in the conceptual model (in Microsoft's designer at least) doesn't set the property in the SSDL model. You have to go and edit the XML by hand...

This situation is described in this Entity Framework Tips and Tricks article.
In Entity Developer you have an opportunity to edit the Store part of the model in Model Explorer in design time.

Hello guys,

I'm just wondering if is second post in this thread up to date best practice how to deal with this scenario?

Thanks

Btw there is MS bug fix for this Entity Framework designer error
http://connect.microsoft.com/VisualStud ... mework-4-0

vmachacek wrote:I'm just wondering if is second post in this thread up to date best practice how to deal with this scenario?

We recommend you to use Entity Developer.
The bug in standard VS Entity Designer is fixed partly and only in VS2010 SP1. A complete fix is available as a separate patch only.
As for the need to create a trigger - this is not necessary any more, because you can create a sequence and bind it the column of the entity: http://www.devart.com/blogs/dotconnect/ ... ggers.html.