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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hepek
Posts: 126
Joined: Thu 07 Jul 2011 13:59

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

Post by 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: 126
Joined: Thu 07 Jul 2011 13:59

Post by 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

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

hepek
Posts: 126
Joined: Thu 07 Jul 2011 13:59

Post by 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 :)

Post Reply