Insert rows with incrementing primary key

Insert rows with incrementing primary key

Postby mpovidlov » Fri 29 May 2009 22:40

I want to insert rows in a table that has a PK column (CUSTOMER_ID). I created a trigger that will read nextval from an Oracle sequence (ID_SEQ) and assign it to PK. This approach forces me to commit on every record. Is there a way to read the nextval manually using the Entities' database connection either by plain SQL (select id_seq.nextval from dual) or by some generated method? This would let me saving the changes at the very end.
mpovidlov
 
Posts: 20
Joined: Tue 06 Jan 2009 00:34
Location: US

Postby AndreyR » Mon 01 Jun 2009 14:15

The first way is to set the StoreGeneratedPattern property of the column in Model.Store to "Identity".
All values will be populated after the SaveChanges() call.
But if you use the generated IDs in code before the SaveChanges() method is called, this approach is inacceptable.
You should create a stored function returning this id in your database and then call the function
from your code like in the following example:
Code: Select all
DataSourceModel1.DataSourceModel1Entities db = new DataSourceModel1.DataSourceModel1Entities();
ObjectQuery query = new ObjectQuery("DataSourceModel1.Store.SELECT_FROM_AUTOINC_SEQ()", db);
decimal i = query.ToList().First();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby mpovidlov » Wed 03 Jun 2009 22:25

Well, I created Oracle functions GET_NEXT_CUSTOMER_ID() and GET_NEXT_DEPT_ID(). The Entity Developer generated corresponding methods after I dragged the stored functions to the context. Somehow it did not recognize that they are functions, not procedures and I had to change the Method Type from StoredProcedure to ScalarValuedFunction manually, otherwise they would not work.
Next, I tried to delay committing into the database until the last moment and wanted to get the next customer_id for each customer by the generated method, assign it to a customer, add the customers to the context. I wanted to call SubmitChanges after the main loop of adding customers. But I get the following exception: Can not insert entity with the same key if key is not database generated.
I was not using the code you suggested (I switched to LINQ to SQL). My path seemed to be straightforward (well, it was not customer, it was marker):


Code: Select all
                    var nextMarkerId = GetNextMarkerId();
                    var marker = new Marker
                                     {...
                                         MarkerId = nextMarkerId,
                                      ...
                                      }
                    ContextM.Markers.InsertOnSubmit( marker );

On the next InsertOnSubmit that comes in a loop it would throw the exception. So as a result I have to SubmitChanges every Insert which does not let me to commit only at the end. What did I miss?
Thanks
mpovidlov
 
Posts: 20
Joined: Tue 06 Jan 2009 00:34
Location: US

Postby AndreyR » Fri 05 Jun 2009 11:05

There are two possible approaches.
1. You do not set the primary key values at all, set the Auto Generated Values property of the primary key column to True,
set the AutoSync property to OnInsert.
In this case you have all primary key values populated only after the SubmitChanges() method is called.
2. Delete the trigger, create a method returning the nextval of your sequence and populate the primary key
of every entity calling this method.
In this case you have the complete control over the primary keys but you make a roundtrip to database on every entity creation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle