New Posts New Posts RSS Feed: Duplicate Key Error When Deleting Then Creating Similar Entity
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Duplicate Key Error When Deleting Then Creating Similar Entity
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Topic: Duplicate Key Error When Deleting Then Creating Similar Entity
    Posted: 24-Feb-2011 at 4:47pm
Hi Jason,
 
I found what the issue is. The issue is that I mistakenly provided you all with inaccurate information.
 
When I stated
"This fix is available in DevForce v6.0.8.1."
 
I really meant
"This fix WILL BE available in DevForce v6.0.9.0."
 
I often need to remind myself that sometimes I'm not running the latest official version of DevForce, but rather a more recent internal build.
 
My apologies,
   Silvio.
Back to Top
jsobell View Drop Down
Groupie
Groupie
Avatar

Joined: 02-Apr-2009
Location: Australia
Posts: 80 		Post Options Post Options   Quote jsobell Quote  Post ReplyReply Direct Link To This Post Posted: 24-Feb-2011 at 1:42pm
I notice you stated "a column with unique value (not the PK) is causing a Duplicate key error".
Does the code check the columns for unique indexes as part of the solution? If so, could it be our use of a compound index?
 
Cheers,
 Jason
Back to Top
jsobell View Drop Down
Groupie
Groupie
Avatar

Joined: 02-Apr-2009
Location: Australia
Posts: 80 		Post Options Post Options   Quote jsobell Quote  Post ReplyReply Direct Link To This Post Posted: 24-Feb-2011 at 1:41pm
As I said, a unique index on the Order in addition to the PK.  This index is a compound of two short strings, and it's this index that causes the error.
We never supply the PK value to any of our entities.
 
Cheers,
 Jason
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 24-Feb-2011 at 9:13am
Hi gkneo,
 
The behavior I refer to is the initial behavior stated by Robert, where a column with unique value (not the PK) is causing a Duplicate key error in the server when an Entity with this value is deleted, a new one with this same value is added, and then a SaveChanges is called.
 
JSobell,
Are you getting the error for the PK? Like I mentioned to Robert initially: (which ended up not being his case)
"On principle, a key should be a unique identifier to an entity and should not be reused.
Ideally you should not use the same key on 2 entities, even if one of the entities is to be removed."
 
Silvio.
Back to Top
jsobell View Drop Down
Groupie
Groupie
Avatar

Joined: 02-Apr-2009
Location: Australia
Posts: 80 		Post Options Post Options   Quote jsobell Quote  Post ReplyReply Direct Link To This Post Posted: 24-Feb-2011 at 5:48am
That may be the case, but we are still experiencing this error.
Our workaround is to save change an entity immediately after it's deletion.
If I get time I'll try and knock up a sample reproducing the error, but it's a simple structure, with Orders/Items tables joined by FK, a unique index on the Order in addition to the PK, and a cascading delete on the FK relationship.
Our process is:
  1. Delete existing Order record (deleting all child Items in the process)
  2. Recreate Order with new child Items
  3. Save to database
Quite simple, but we still get duplicate key errors when DF 6.0.8.1 tries to write the new version before deleting the old one :/
 
Back to Top
gkneo View Drop Down
Newbie
Newbie
Avatar

Joined: 23-Jun-2010
Posts: 21 		Post Options Post Options   Quote gkneo Quote  Post ReplyReply Direct Link To This Post Posted: 24-Feb-2011 at 1:49am
Hi, Silvio.

Which behavior was different?
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 17-Feb-2011 at 10:32pm
Hi Jason,
 
DevForce's behavior was different that EF's in the scenario above. So we fixed it in DevForce to work as it does the EF.
This fix is available in DevForce v6.0.8.1. This fix WILL BE available in DevForce v6.0.9.0.
 
Silvio.
 


Edited by sbelini - 24-Feb-2011 at 4:48pm
Back to Top
jsobell View Drop Down
Groupie
Groupie
Avatar

Joined: 02-Apr-2009
Location: Australia
Posts: 80 		Post Options Post Options   Quote jsobell Quote  Post ReplyReply Direct Link To This Post Posted: 17-Feb-2011 at 8:36pm
So have we confirmed this is an EF bug?
We've hit this same issue (and have just spent hours finding it), so we're intrerested in the solution options.

Is there a 'workaround' such as rearranging the entities passed to EF so that the deletes occurr before the inserts?

Cheers,
 Jason
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 28-Jan-2011 at 10:00am
Hi Robert,
 
After discussing the issue with our senior engineer, that was the only idea we could come up with in order to keep all operations transactional.
 
It is a bit of work indeed, but it will ensure data integrity if anything goes wrong along the way.
 
My apologies for the inconvenience.
 
Silvio.
Back to Top
Robert R View Drop Down
Newbie
Newbie


Joined: 17-Aug-2010
Posts: 7 		Post Options Post Options   Quote Robert R Quote  Post ReplyReply Direct Link To This Post Posted: 28-Jan-2011 at 6:47am
Conceptually, from the end user's perspective, this work should all be done in one transaction.  If that can be accomplished across multiple database transactions, that is fine.  My concern is that if the first SaveChanges() succeeds but the second fails, we will need a lot of additional code to do the equivalent of a rollback to the first transaction.  Also, if the second SaveChanges failed because of a network error or database server issue, that same issue may make it impossible to rollback the committed portion of the work, leaving the data in an inconsistent state.
 
Using TransactionScope in n-tier seems like a lot of work, but we may need to proceed that way in order to ensure our customers' data integrity.
 
Are there any other ideas we should consider, or is that our best option for meeting our requirements?
 
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 28-Jan-2011 at 12:40am

Robert, 

Is it absolutely necessary to have both SaveChanges in one transaction? The simplest approach would be having to partial SaveChanges ( mgr.SaveChanges( new Employee[] {empToBeDeleted})  ).
 
If having all in one transaction is a must, you could still wrap the 2 SaveChanges inside TransactionScope while using DevForce. However, it would work in 2-tier only.
 
If your app is running n-tier, you would be able to accomplish it, but the 'wrapping' would have to be done on the server.
The alternative then would be invoking a server method and doing the SaveChanges on the server:
 
[AllowRpc]
public static EntityCacheState SaveEmployee(IPrincipal principal, EntityManager entityManager, params Object[]
args) {
  Employee empToDelete = args[0] as Employee;
  Employee empToAdd = args[1] as Employee;
  try {
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = TransactionSettings.Default.IsolationLevel;
    using (TransactionScope scope = new TransactionScope(System.Transactions.TransactionScopeOption.Required,
transactionOptions)) {
      entityManager.SaveChanges(new Employee[] { empToDelete });
      entityManager.SaveChanges(new Employee[] { empToAdd });
      scope.Complete();
    }
  } catch (TransactionAbortedException ex) {
    // handle exception
    return null;
   }
  return entityManager.CacheStateManager.GetCacheState();
}
 
Note that once you merge the returned EntityCacheState with the EntityCacheState of the EM in the client, you still will have to clean up the unwanted entities (i.e. the entity with Deleted and Added EntityState)
 
...
EntityCacheState ecs = (EntityCacheState)mgr.InvokeServerMethod(typeName, methodName, empToDelete, empToAdd);
mgr.CacheStateManager.RestoreCacheState(ecs);
mgr.RemoveEntities(new Employee[] { empToDelete, empToAdd});
...
 
Silvio.
Back to Top
Robert R View Drop Down
Newbie
Newbie


Joined: 17-Aug-2010
Posts: 7 		Post Options Post Options   Quote Robert R Quote  Post ReplyReply Direct Link To This Post Posted: 27-Jan-2011 at 11:55am
Since I wasn't sure whether this was more of a DevForce or Entity Framework issue, I also posted it to the appropriate MSDN forum.  The response I received there was to wrap the two SaveChanges inside a single TransactionScope.  Is it possible to do that when using DevForce?
Back to Top
Robert R View Drop Down
Newbie
Newbie


Joined: 17-Aug-2010
Posts: 7 		Post Options Post Options   Quote Robert R Quote  Post ReplyReply Direct Link To This Post Posted: 27-Jan-2011 at 11:40am
We get the exception when calling SaveChanges.  The exception is a SQL Server duplicate key error.
 
The new entity does not have the same primary key as the deleted entity.  We are generating a unique surrogate key for each new entity that we create.
 
The new entity does match the deleted entity on properties which map to database columns that we are requiring unique values on.  The uniqueness of those columns is being enforced by an alternate key.  It is on that alternate key, not on the primary key, that we are getting a duplicate key error.
 
We are deleting the original entity before creating the new entity, but the SQL insert and delete statements are being exeduted in the opposite order.  Is there any way to have the deletes occur before the inserts?
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 26-Jan-2011 at 12:26pm

Hi Robert,

 
Are you getting the exception when you call SaveChanges or when you add the new entity to the entity manager?
What is the exception message you are getting?
 
Like you mentioned, the new entity has the same data as the deleted one.
On principle, a key should be a unique identifier to an entity and should not be reused.
Ideally you should not use the same key on 2 entities, even if one of the entities is to be removed.
 
Silvio.
Back to Top
Robert R View Drop Down
Newbie
Newbie


Joined: 17-Aug-2010
Posts: 7 		Post Options Post Options   Quote Robert R Quote  Post ReplyReply Direct Link To This Post Posted: 26-Jan-2011 at 6:16am
We are getting duplicate key errors in the following scenario:
  1. An entity is deleted.
  2. A new entity is created with data that matches the deleted entity on properties covered by a database alternate key to enforce uniqueness.
  3. We then call SaveChanges().  Although the final state of the database transaction would not violate the alternate key, it appears that the insert is occuring before the delete is attempted.
Can anyone recommend a good approach to avoid this issue?
 
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down