object specified for refresh is pending insert and cannot be refreshed RRS feed

  • Question

  • I am applying transaction data to a master file. If the key does not exists in the master, insert a row. If the key does exist, update the row in the master with the latest info from the transaction.  This is done in a standalone method that receives a linq to sql data context as an argument.

    Sometimes there is an sqlexception on the submitchanges part of this method. Within the exception handling code, I do a context.Refresh to clear the insert out of the context, then the code retries the check for the row, insert if missing, update if existing operation.

    Sometimes I am getting an error on the context.refresh.  The error is "An object specified for refresh is pending insert and cannot be refreshed." - InvalidOperationException.  There is no inner exception info.

    Here is the refresh stmt:

         InContext.Refresh(RefreshMode.OverwriteCurrentValues, InContext.MerchantMasters);

    MerchantMasters is the table I am inserting into. The context received as an argument should not have any other pending updates, but even if it does, I understand context.Refresh to only clear the operations to MerchantMasters in this case.

    How do I correctly refresh the context so that I can retry the insert/update?




    Thursday, July 22, 2010 10:25 PM


  • Hi SteveRichter,


    DataContext.Refresh method is typically used to refresh the object state with values from the database, either overwriting the current values or keep the current changes. This method is used  after an optimistic concurrency error to bring items into a state for another attempt. For example : User1 tries to submit changes, but User2 has in the meantime changed corresponding record in the database already. This does not applied to your scenario. The exception for your scenario is Violation of PRIMARY KEY constraint when you want to insert a new row whose identity key has already exist in the database.  So DataContext.Refresh Method is not applicable for the pending insert, that’s why you got the exception saying “An object specified for refresh is pending insert and cannot be refreshed.” 


    I think one way to solve your problem is to check whether the identity key of the new entity already exists in the database before you do the insert operation.


    If you do not want to check it but want to handle the exception once violating the PRIMARY KEY constraint. (That means the record already exist, do not need to insert a new one, just need updating), and if few of the insert operation would violate of PRIMARY KEY constraint, then one way you can do that is first detaching the new entity from the previous DataContext(you need implement the detach functionality), and attach the entity to a new DataContext and call the SubmitChanges on the new DataContext to update. You can refer to the following example:


    NorthWindDataContext db = new NorthWindDataContext();
    Users1 newEE = new Users1();
    newEE.UserID = 5;
    newEE.UserName = "NewUser1234";
    newEE.ClientCodes = "HHHHHH1234";          
    catch (Exception eexx)
      newEE.Detach(); // First detach the entity
      NorthWindDataContext db1 = new NorthWindDataContext(); 


    The following code is used to implement detaching entities.


    partial class Users1 : LinqEntityBase
      public override void Detach()
        if (null == PropertyChanging)
        PropertyChanging = null;
        PropertyChanged = null;
    public abstract partial class LinqEntityBase
      public abstract void Detach();


    If you have other questions, please feel free to let me know.


    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Steve Richter Friday, July 23, 2010 2:56 PM
    Friday, July 23, 2010 9:53 AM