CSLA+EF1.0: error "Cannot insert duplicate key ... with unique index ... " on delete+insert records

roberto posted on Fri, Apr 13 2012 9:48 AM

rated by 0 users

Hi all
  I'm having this problem, and after search on google, found it quite common:

- first, delete a row from a table, or update a specific field (related to unique index)
- then, add new row with same value

example: delete order row item #2 (fieldXYZ=2),then add new row item with same value (fieldXYZ=2),
plus there is a unique index on fieldXYZ

what I noticed is: 

- csla business list tells EF what to do in the right sequence: first delete child object, then insert newchild object

- EF does insert operation before delete, so database index raise error "Cannot insert duplicate key row in object '[....]' with unique index 'IX_[...]'.The statement has been terminated." and I get an exception.

Found same error in these threads "Getting Insert Duplicate Key Error But Only Updating and Deleting", "Get duplicate key exception when deleting and then re-adding child rows with Entity Framework".

I found a solution that seems working good, but I'm not sure it is the right one:

Here is my code, fix in "SaveChanges(false)" in child snippet :

- root object code is similar to this:

        [Transactional(TransactionalTypes.TransactionScope)]
        protected override void DataPortal_Update()
        {
            using (ObjectContextManager<MyEntities> manager = ObjectContextManager<MyEntities>.GetManager(MyConnectionName, true))
            {
              // do some stuff here
              // ... update order header, if necessary
            
              // update child objects, order items
              DataPortal.UpdateChild(ReadProperty(OtherItemListProperty), this, order);
              manager.ObjectContext.SaveChanges();
            }
            FieldManager.UpdateChildren();
        }

- child object code is similar to this:

        private void Child_Insert(Order o, Order orderEF)
        {
            using (ObjectContextManager<MyEntities> manager = ObjectContextManager<MyEntities>.GetManager(MyConnectionName, true))
            {
                OrderItem orderItemEF = manager.ObjectContext.OrderItems.Where("it.fieldXYZ = 4").FirstOrDefault();
                // ... orderItemEF set fields
                orderItemEF.FieldXYZ = 2;  // unique index on this field

                manager.ObjectContext.AddToOrderItems(orderItemEF);

                //  manager.ObjectContext.SaveChanges(false); // WITHOUT THIS, ERROR RAISED ON UNIQUE INDEX
            }
            FieldManager.UpdateChildren();
        }

Is it the right solution? or are there better ones ?

ps: in this project, EF is 1.0; should (have) I update to 4.0 ?
I would like not to, because I have third part libraries not updated to fw 4.0 .

thank you very much