Can not insert entity with the same key if key is not databa

Can not insert entity with the same key if key is not databa

Postby hepek » Fri 30 Mar 2012 18:05

I am trying to insert a new records in comp_style table. Instead of updating records I delete them all first and then re-create them using the same PK as they had before, in order to keep the same id’s.

I get the Exception "Can not insert entity with the same key if key is not database generated". I am using dotConnect for Oracle version 6.70.

If records were not deleted first I would expect to get this error, since it would be a duplicate key.
But I delete records first, so the record I am about to create is brand new record, and PK IS NOT REPEATING since it is already deleted.

below is my code (simplified):

Code: Select all
using (DbTransaction transaction = DB.Connection.BeginTransaction()) {
   // first I delete set of rows from comp_Style table
    context.ExecuteCommand("DELETE FROM comp_Style WHERE job_num = 1");
   
   ......
   
   // later on in a code I recreate the rows I deleted
   CompStyle styleDB = new CompStyle();
   
   styleDB.CounterId = counterID;
   // CounterId is Primary Key, I am reusing the same ID(counterID) from the record I deleted previously

   context.CompStyles.InsertOnSubmit(styleDB);
   // this is when I get the exception
}


thank you
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59

Postby hepek » Fri 30 Mar 2012 19:31

as a test, I queried the database just before I insert the new record

Code: Select all
var test = context.CompStyles.Where(x => x.CounterId == styleDB.CounterId).SingleOrDefault();


context.CompStyles.InsertOnSubmit(styleDB);

.........



test returns null, which is expected since I deleted the record previously.
so, why is dotConnect complaining about duplicate key. is it a bug perhaps?

thanks
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59

Postby MariiaI » Tue 03 Apr 2012 13:44

If you retrieve the data to the DataContext and then delete the records from the database using the context.ExecuteCommand("DELETE FROM comp_Style"), the context context still contains the data and "doesn't know" that the rows have been deleted from the database. The method context.ExecuteCommand() executes SQL command directly on the database while selected entities are cached and DataContext works with cache.
Please use
Code: Select all
IEnumerable compStyles = (from c in context.CompStyles select c).ToList();
context.CompStyles.DeleteAllOnSubmit(compStyles);
context.SubmitChanges();
instead of
Code: Select all
context.ExecuteCommand("DELETE FROM comp_Style");

Or you could use new DataContext for inserting new rows. In this case the cache will be empty and clean.
Code: Select all
YourDataContext contextNEW = new YourDataContext();
contextNEW.CompStyles.InsertOnSubmit(styleDB);

Please notify us if this information helps.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Postby hepek » Tue 03 Apr 2012 14:12

Thank you Mariaa,

That is very helpful explanation and makes a lot of sense.

Keep doing a good work :)
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59


Return to dotConnect for Oracle