Insert sequence.NEXT_VAL in Entity Framework.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
willfer
Posts: 1
Joined: Tue 19 Jan 2010 00:24

Insert sequence.NEXT_VAL in Entity Framework.

Post by willfer » Tue 19 Jan 2010 00:29

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?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 19 Jan 2010 10:11

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.

tcurtis
Posts: 13
Joined: Thu 14 Jan 2010 16:52

Re: Insert sequence.NEXT_VAL in Entity Framework.

Post by tcurtis » Tue 19 Jan 2010 11:42

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.

jeffro
Posts: 2
Joined: Mon 05 Apr 2010 23:04

Post by jeffro » Mon 05 Apr 2010 23:10

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.)

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 07 Apr 2010 08:51

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.

mabraham1
Posts: 6
Joined: Wed 21 Apr 2010 22:27

Does 1.0.31.0 Offer Identity on Insert?

Post by mabraham1 » Thu 22 Apr 2010 04:16

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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 22 Apr 2010 12:31

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.

piers7
Posts: 5
Joined: Wed 29 Jun 2011 07:12
Contact:

Be sure to set StoreGeneratedPattern *in the XML*

Post by piers7 » Fri 01 Jul 2011 05:57

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...

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 05 Jul 2011 12:54

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.

vmachacek
Posts: 4
Joined: Mon 12 Sep 2011 08:59

Post by vmachacek » Mon 12 Sep 2011 09:05

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 13 Sep 2011 11:25

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.

Post Reply