none
Commit single, specific entities or prevent specific entities from being committed RRS feed

  • Question

  • Hi all,

    We regularly come across situations where we need to insert/update only a specific, single object or objects regardless what else has changed and we cannot seem to do this in LINQ to SQL. Sounds simple enough but I'm completely stuck. Any ideas?

    Regards,

    Stephen
    Wednesday, November 25, 2009 6:16 PM

Answers

  • Hello Stephen,

     

    Welcome to LINQ to SQL forum!

     

    The SubmitChanges commits the changes for the whole graph.  To only insert/update single entity, I can figure out two options:

     

    1.      Detach the entity and attach it to another DataContext for updating entity.  Using another DataContext to insert new entities. 

    We need to refresh the original DataContext or delete the entity from the original DataContext.  Otherwise if we call SubmitChanges on the original DataContext, it will insert/update the entity again. 

     

    Update method:

    ============================================================================================
    Update(entity);

            originalContext.Refresh(RefreshMode.OverwriteCurrentValues, entity);

    originalContext.SubmitChanges();

     

    private void Update(EntityType entity)

            {

                using (MyDataContext db = new MyDataContext())

                {

                    qq.Detach();

                    db.EntitySet.Attach(entity);

                    db.Refresh(RefreshMode.KeepCurrentValues, entity);

                    db.SubmitChanges();

                }

    }
    ============================================================================================

     

    For the Detach method of the entity, please see http://msmvps.com/blogs/omar/archive/2007/12/08/linq-to-sql-how-to-attach-object-to-a-different-data-context.aspx.  

     

    Insert method:

    ============================================================================================
    Insert(entity);

            originalContext.EntitySet.DeleteOnSubmit(entity);

    originalContext.SubmitChanges();

     

            private void Insert(EntityType entity)

            {

                using (DataClasses4DataContext db = new DataClasses4DataContext())

                {

                    db.Qs.InsertOnSubmit(entity);

                    db.SubmitChanges();

                }

    }
    ============================================================================================

     

    2.      Use the DataContext.ExecuteCommand method to manually insert/update entities.  The T-SQL needs to be created by our own and we also need to refresh or delete the entity in the original DataContext.

     

     

    Additionally, I recommend you to shorten the DataContext’s lifetime and create a new one after a unit of work.  http://blogs.msdn.com/dinesh.kulkarni/archive/2008/04/27/lifetime-of-a-linq-to-sql-datacontext.aspx.

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Thursday, November 26, 2009 3:53 AM
    Moderator
  • Hello Stephen,

     

    I did not reproduce the problem as you have mentioned if I tried to update an entity with the sample codes I provided.  Also the association is updated correctly.  Of course, I modify the relationship from one entity to another.  Both the entities exist in the database.  If you want to update the association to some newly created entities, I think we need to first add the new entity and then update the existing entity.  

     

    If you have any questions, please feel free to let me know.   If it is convenient, could you please provide me with some sample codes to investigate? 

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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 sroughley Wednesday, December 2, 2009 1:14 PM
    Friday, November 27, 2009 6:50 AM
    Moderator

All replies

  • Hello Stephen,

     

    Welcome to LINQ to SQL forum!

     

    The SubmitChanges commits the changes for the whole graph.  To only insert/update single entity, I can figure out two options:

     

    1.      Detach the entity and attach it to another DataContext for updating entity.  Using another DataContext to insert new entities. 

    We need to refresh the original DataContext or delete the entity from the original DataContext.  Otherwise if we call SubmitChanges on the original DataContext, it will insert/update the entity again. 

     

    Update method:

    ============================================================================================
    Update(entity);

            originalContext.Refresh(RefreshMode.OverwriteCurrentValues, entity);

    originalContext.SubmitChanges();

     

    private void Update(EntityType entity)

            {

                using (MyDataContext db = new MyDataContext())

                {

                    qq.Detach();

                    db.EntitySet.Attach(entity);

                    db.Refresh(RefreshMode.KeepCurrentValues, entity);

                    db.SubmitChanges();

                }

    }
    ============================================================================================

     

    For the Detach method of the entity, please see http://msmvps.com/blogs/omar/archive/2007/12/08/linq-to-sql-how-to-attach-object-to-a-different-data-context.aspx.  

     

    Insert method:

    ============================================================================================
    Insert(entity);

            originalContext.EntitySet.DeleteOnSubmit(entity);

    originalContext.SubmitChanges();

     

            private void Insert(EntityType entity)

            {

                using (DataClasses4DataContext db = new DataClasses4DataContext())

                {

                    db.Qs.InsertOnSubmit(entity);

                    db.SubmitChanges();

                }

    }
    ============================================================================================

     

    2.      Use the DataContext.ExecuteCommand method to manually insert/update entities.  The T-SQL needs to be created by our own and we also need to refresh or delete the entity in the original DataContext.

     

     

    Additionally, I recommend you to shorten the DataContext’s lifetime and create a new one after a unit of work.  http://blogs.msdn.com/dinesh.kulkarni/archive/2008/04/27/lifetime-of-a-linq-to-sql-datacontext.aspx.

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Thursday, November 26, 2009 3:53 AM
    Moderator
  • Thank you for your repply. I had been investigating detaching the entity to achieve this but am a little concerned about the fact that I have to remove the associations before committing the entity. Will this not cause any foreign key values to be set null in the database?
    Thursday, November 26, 2009 4:16 PM
  • Hello Stephen,

     

    I did not reproduce the problem as you have mentioned if I tried to update an entity with the sample codes I provided.  Also the association is updated correctly.  Of course, I modify the relationship from one entity to another.  Both the entities exist in the database.  If you want to update the association to some newly created entities, I think we need to first add the new entity and then update the existing entity.  

     

    If you have any questions, please feel free to let me know.   If it is convenient, could you please provide me with some sample codes to investigate? 

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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 sroughley Wednesday, December 2, 2009 1:14 PM
    Friday, November 27, 2009 6:50 AM
    Moderator