Page 1 of 1

Insert rows with incrementing primary key

Posted: Fri 29 May 2009 22:40
by mpovidlov
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.

Posted: Mon 01 Jun 2009 14:15
by AndreyR
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();

Posted: Wed 03 Jun 2009 22:25
by mpovidlov
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

Posted: Fri 05 Jun 2009 11:05
by AndreyR
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.