none
Commiting DELETEs before INSERTs RRS feed

  • Question

  • Hello,

    I have a case where I need to delete a record and then insert another record in a single transaction. However, the INSERT is occuring before the DELETE, which violates a constraint on my database. Is there any way to perform the DELETE before the INSERT when calling SubmitChanges()? The operations are invoked by a GUI so there's no real way to separate them, without detecting such a case(difficult anyway) and prompting the user "You must save your changes before doing this" (undesireable, and I want to do it in a single transaction anyway).

    Can anyone help?

    Cheers,
    Steven
    Friday, June 26, 2009 12:30 PM

All replies

  • Do you have association properties set up in your DBML?  (These should be generated automatically if you have foreign key constraints in your database).

    If so, the ordering of inserts/deletes should be correct.

    Joe

    Write LINQ queries interactively - www.linqpad.net
    Friday, June 26, 2009 12:49 PM
    Answerer
  • Yes, I have foreign key constraints in the DB and association properties in the DBML. This is relevant portion of what I have:

    Table: Yarn
    Primary key: YarnId

    Table: YarnLibrary
    Primary key: YarnLibraryId

    Table: LibraryMember
    Primary key: LibraryMemberId
    Foreign key: YarnId
    Foreign key: YarnLibraryId
    Unique constraint: (YarnId, YarnLibraryId)

    When I load up the application (WPF) there is a LibraryMember record linking yarn Y -> library L. The user removes Y from L so I DeleteOnSubmit() that record.

    The user then realises they've done something wrong so they add Y back into L. I InsertOnSubmit() a new LibraryMember record.

    When I call SubmitChanges(), LINQ to SQL attempts to INSERT before DELETE, causing a unique constraint error on (YarnId, YarnLibraryId).

    So I guess I need to let LINQ to SQL know about the unique constraint on (YarnId, YarnLibraryId)? 
    Friday, June 26, 2009 1:52 PM
  • I have come up with two solutions to this problem, which I've detailed over at stackoverflow.com: 


    However, what seems like the best solution (Attach()ing entities to a new data context) has some problems. I would be interested to hear members of this forum's thoughts on this matter. I'm sure this isn't such an unusual problem?
    Tuesday, June 30, 2009 8:07 AM
  • I'm not sure I understand why you delete and then re-insert instead of just updating..? But if that's the way you want to do it, why not in two steps? First delete+submitchanges, then insert+submitchanges. Wrap it in a TransactionScope to make it atomic.
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Tuesday, June 30, 2009 9:11 AM
    Answerer
  • I delete and re-insert because it's all done from a WPF appliation in response to a user unckecking/re-checking a check box. Uncheck calls DeleteOnSubmit() on the underlying entity, checking the box InsertOnSubmit()s a new record.

    When the user checks the checkbox, I've tried searching through the Deletes change set for an already deleted entity to re-insert, but once an entity is deleted its foreign keys are set to 0. So without keeping a separate list containing copies of all deleted entities, there's nothing i can do but insert a new record.

    In the Attach() scenario I linked to, I do "delete+submitchanges, then insert+submitchanges". However I can't find a way to do this on the original DataContext. Am I missing something?

    Tuesday, June 30, 2009 9:23 AM
  • Dont mistake my answer. I have never been a great fan of Attach considering the fact that one needs to create a new DataContext every single time. Another reason, I ran into too many problems. If I were you, I would DeleteOnSubmit(), and then InsertOnSubmit() and then SubmitChanges in a TransactionScope. This will preseve the order and make it atomic.
    Wednesday, July 1, 2009 2:06 PM
  • Thanks Praveen,

    How do I then bring my original DataContext back in sync with the changes committed using the other DataContext? 

    The ViewModels in the application still have refences to the entities in the original DataContext following a submit, so I can't throw it away and create a new one without having to entirely rebuild my application's state. If I try to do a Refresh(), it complains that records have been deleted from the database - it can't seem to cope.

    If I don't sync up the data context, then the change set will still contain changes that have actually already been sent to the database, so next time I submit they will be sent again.
    Wednesday, July 1, 2009 2:12 PM
  • And sorry, I don't understand this sentence:

    If I were you, I would DeleteOnSubmit(), and then InsertOnSubmit() and then SubmitChanges in a TransactionScope. This will preseve the order and make it atomic.

    How do I make it preserve the order?
    Wednesday, July 1, 2009 2:13 PM
  • Hi Grokys,

    Have you find a solution ? I have exactly the same problem

    Thank

    Wednesday, February 2, 2011 10:26 AM
  • I had the same problem.

    The solution, which i've found, utilized InsertYourEntity(YourEntity instance) and DeleteYourEntity(YourEntity instance) methods. Unfortunately it seems impossible to hook any (generic) insert/delete. So the solution was for the particular problematic entity only, but this was fine for me.

    In a custom part of my data context class i declared a private instance member "defferedInsertsOfMyEntity" as a dictionary, where key was a structure identical to the unique key in the database and value was the entity instance which during the current SubmitChanges() was to be inserted after another entity with the same key was deleted.

    partial class MyDataContext
    {
      /// <summary>
      /// List of entity objects which cannot be inserted now but should be inserted within the same SubmitChanges() method, after an object
      /// sharing the same logical unique key is deleted. LINQ is not smart enough to run DELETE prior to INSERT.
      /// I force it doing so for this one entity as it is a common scenario that MyEntity is deleted
      /// and a new is created with the same logical key but different data.
      /// </summary>
      private Dictionary<MyEntity.UniqueKeyStructure, MyEntity> deferredInsertOfMyEntity = null;
    
      partial void InsertMyEntity(MyEntity instance)
      {
        //Any deletes must go first:
        if (deferredInsertOfMyEntity == null)
        {
          deferredInsertOfMyEntity = new Dictionary<MyEntity.UniqueKeyStructure, MyEntity>();
    
          var changeSet = GetChangeSet();
          var inserts = changeSet.Inserts.OfType<MyEntity>();
          var deletesByKey = changeSet.Deletes.OfType<MyEntity>().ToDictionary(entry => entry.UniqueKey);
    
          foreach (var insert in inserts)
          {
            if (deletesByKey.ContainsKey(insert.UniqueKey))
            {
              if (deferredInsertOfMyEntity.ContainsKey(insert.UniqueKey))
              {
                throw new System.Data.Linq.DuplicateKeyException(insert.UniqueKey, 
                  "Attempt to insert more than one object with key " + insert.UniqueKey + ": " 
                  + insert + " and " + deferredInsertOfMyEntity[insert.UniqueKey] + ".");
              }
    
              deferredInsertOfMyEntity.Add(insert.UniqueKey, insert);
            }
          }
        }
    
        if (!deferredInsertOfMyEntity.ContainsKey(instance.UniqueKey))
        {
          //Call the standard LINQ routine
          ExecuteDynamicInsert(instance);
        }
        //Otherwise the INSERT will be executed within DeleteMyEntity after the corresponding DELETE takes place.
      }
    
      partial void DeleteMyEntity(MyEntity instance)
      {
        //Call the standard LINQ routine
        ExecuteDynamicDelete(instance);
    
        if (deferredInsertOfMyEntity != null
          && deferredInsertOfMyEntity.ContainsKey(instance.UniqueKey))
        {
          //Call the standard LINQ routine
          ExecuteDynamicInsert(deferredInsertOfMyEntity[instance.UniqueKey]);
        }
      }
    }
    
    Obviously MyEntity.UniqueKeyStructure has to implement appropriate Equals() and GetHashCode() methods.
    Friday, February 11, 2011 2:41 PM