none
Entity Framework does Rowversion checking for parent tables? RRS feed

  • Question

  • I'm having a problem where i'm making changes to one table, and the system is checking the rowversion of dependent tables for concurrency purposes. 

    I have two tables.  ForeignCurrencyTransaction and Currency.  The ForeignCurrencyTransaction has a "CurrencyID" field and a foreign key to the Currency table.  Both tables have a RowVersion column.  In my model, I've brought in both tables, and the navigation properties are created.  It's correctly identified the RowVersion column as a computed column.  I also set this column to be "Concurrency Mode = Fixed" in my model on both tables.  Although currencies could be changed, it's unlikely, but I still want to keep the property set.

    Now, I retrieve a single ForeignCurrencyTransaction into memory, and include the currency in the object query.  In my context, I now have the a ForeignCurrencyTransaction and a Currency Object.  I update the "Amount" field on the ForeignCurrencyTransaction and save the context.

    As a result of the save, the system is issuing two statements.  The second one is the update of the amount on the ForeignCurrencyTransaction.  The first one is below:

    exec sp_executesql N'declare @p int
    update [dbo].[TBgeoCURRENCY]
    set @p = 0
    where (([CurrencyId] = @0) and ([RowVersion] = @1))
    select [RowVersion]
    from [dbo].[TBgeoCURRENCY]
    where @@ROWCOUNT > 0 and [CurrencyId] = @0',N'@0 bigint,@1 binary(8)',@0=45,@1=0x00000000000CC774

    As a result of this statement, the RowVersion of the Currency table is updated. 

    In a multi-user system, different user will retrieve items such as Currency at different points in time.  These objects are used in the system, attached to other objects which are then eventually saved.  The Currency object itself is never changed (except rarely).  However, this code ensures that no other user is able to save their changes without getting a concurrency exception - not on the object they're trying to save (ForeignCurrencyTransaction), but rather on a parent object (Currency) which has not changed.  The only solution I've found is to do refresh of each parent object in the context before issuing the savechanges.  But even that can fall victim to issues of timing (and never mind how much extra DB churn it generates for no good reason). I don't even want to discuss the effect this has on our caching strategy...

    One suggestion might be to turn off concurrency checking on parent objects, but unfortunately, the above example is a bit of an oversimplification and we have other objects in the system where the users might legitimately be editing and making changes to parent objects, and we need concurrency checking on those parent objects.  We do not need concurrency checking on the parents of the objects being edited.

    Any thoughts?  Is there a way to turn off this behavior and only get concurrency checking on the objects being edited?  Maybe there is a way to have SQLServer not generate a new rowversion when these is no actual change?

    Thanks,

    Fredrik.

     

    • Moved by Rowan MillerModerator Sunday, August 15, 2010 1:49 AM Moving from pre-release forum as this thread relates to a released product (From:ADO.NET Entity Framework and LINQ to Entities (Pre-Release))
    Friday, August 13, 2010 11:12 PM

Answers

  • Hi Fredrik,

     

    Thank you very much for providing us with the demo project!   With the demo, I can repro the issue.   I believe it’s related to a known issue which I have mentioned in my first post.   But the current issue seems to be more incredible.  The Current table has not been updated.   It’s only been loaded and accessed when we perform the databinding.  

     

    I also noticed that if I turn off the LazyLoadingEnabled property and remove the Include(“Currency”) in the LINQ query, the issue does not occur.  

     

    I would recommend you open a ticket in Microsoft Connect to report this issue to the product team.   Please share the link here to benefit more community members.    

     

    Thanks again, Fredrik, & have a nice weekend!

     

     

    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.
    Friday, September 3, 2010 2:26 PM
    Moderator
  • This is a known issue in Entity Framework 4.0 - the issue does not affect everyone as it is mostly limited to cases where you use ConcurrencyMode Fixed on principal end, in addition to Foreign Key associations that were introduced in .NET 4.0.

    Rest assured that we are working on addressing the issue.

    Thanks for sending us the feedback.

    Faisal Mohamood | Program Manager | Entity Framework

    Wednesday, September 8, 2010 2:24 AM

All replies

  • Hello Fredrik,

     

    Welcome to EF forum!

     

    I once saw a similar issue one or two month ago.   The product team has confirmed that it’s a product issue in EF4.   The workaround would be update the entities via foreign key properties instead of via navigation properties directly, like:

    =======================================================================

    Session session = new Session() { SessionID = 1, Time = DateTime.Now };

    session.UserID = 1;

    session.WorkstationID = 1;

    context.Sessions.AddObject(session);

    context.SaveChanges();

    =======================================================================

    Here, User has multiple Sessions and Workstation also has multiple Sessions.

     

    Good 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.
    Monday, August 16, 2010 9:14 AM
    Moderator
  • Hi Fredrik,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    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.
    Wednesday, August 18, 2010 1:21 AM
    Moderator
  • Hi Lingzhi,

    Thanks for the suggestion, but unfortunately that does not help me.  In the example that I provided, I was not even touching the relationship, but an attribute on an object (ForeignCurrencyTransaction) that has a relationship to another object (Currency).  If I don't retrieve the Currency object as part of the original query, then I don't seem to have this problem.  However, I also don't have a CurrencyName to show on the form where the user is editing.  I've tested the ObjectContext before saving, and the ObjectStateManager reports no changes to the Currency object.  Yet, the generated SQL code continues to update the Currency record in this way.

    Now, I've solved my initial problem by abandoning the RowVersion as the basis for concurrency checking, using my AuditDateTime (datetime field) instead.  It won't be as fool-proof as a RowVersion field, but it will be good enough.  The system still issues the update against the Currency table, but it no longer causes other forms to have concurrency issues.

    However, I've since applied my security model to the database.  The security model does not grant anyone outside of administrators the right to update the Currency table.  This is not a table that changes very often and access to it should be tightly controlled.  The accounting users role has permission to update the ForeignCurrencyTransaction object, but does not have the permissions to update the Currency Object.  Because of this code, however, they are getting permission denied on posting their ForeignCurrencyTransaction changes because of the un-necessary update to the Currency table.  This forces me to create a security model where everyone has update permissions on all objects that are upstream of the objects they are supposed to be allowed to update.

    I'm having a hard time understanding how Microsoft could have intended this behavior.  I've been working with EF 3.5 for 1.5 years now, and this is my first EF 4.0 project.  So far, it's not an improvement, but rather a step back....What am I doing wrong?

    Fredrik.

    Thursday, August 26, 2010 9:52 PM
  • Hi Lingzhi,

    I've created a sample project that showcases the behavior.  It consists of:

    • SQLScript to create the two tables, relationships, and seed some data.
    • VB.NET application with an EF model of the database and a form that first retrieves some data, allows the user to change one field, and a save of the data back to the database.
    • A SQL trace file showing the SQL that is issued (10 lines of code).

    This sample shows how the update against the Currency table is issued, even though the ObjectStateManager reports no changes against the currency object.  It doesn't demonstrate the concurrency issue since that code would be more complex (involving two contexts).  I wanted to keep it down to the basics.  However, you can see how the update to Currency changes the RowVersion of the currency - causing a concurrency problem for everyone else who is also using the same currency. 

    Unfortunately, I was hoping I could upload a zip file here, but I can't.  Any thoughts about how I could get the sample to you?

    Thanks,

    Fredrik.

    Friday, August 27, 2010 3:42 PM
  • Hi Fredrik,

     

    Thanks for your following up!  You can directly ping me at v-micsun@microsoft.com

     

     

    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.
    Monday, August 30, 2010 3:38 AM
    Moderator
  • Hi Fredrik,

     

    Thank you very much for providing us with the demo project!   With the demo, I can repro the issue.   I believe it’s related to a known issue which I have mentioned in my first post.   But the current issue seems to be more incredible.  The Current table has not been updated.   It’s only been loaded and accessed when we perform the databinding.  

     

    I also noticed that if I turn off the LazyLoadingEnabled property and remove the Include(“Currency”) in the LINQ query, the issue does not occur.  

     

    I would recommend you open a ticket in Microsoft Connect to report this issue to the product team.   Please share the link here to benefit more community members.    

     

    Thanks again, Fredrik, & have a nice weekend!

     

     

    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.
    Friday, September 3, 2010 2:26 PM
    Moderator
  • Hi Lingzhi,

    Thanks for your help on this.

    I've opened an issue on Microsoft Connect for this (https://connect.microsoft.com/data/feedback/details/595724/entity-framework-does-rowversion-checking-for-parent-tables)

    If this bug is real, I don't see how everyone is not affected by it.  It would seem to affect everyone who retrieves a parent table as part of a query and then updates the child table. 

    Thanks,

    Fredrik.

    Tuesday, September 7, 2010 12:55 PM
  • This is a known issue in Entity Framework 4.0 - the issue does not affect everyone as it is mostly limited to cases where you use ConcurrencyMode Fixed on principal end, in addition to Foreign Key associations that were introduced in .NET 4.0.

    Rest assured that we are working on addressing the issue.

    Thanks for sending us the feedback.

    Faisal Mohamood | Program Manager | Entity Framework

    Wednesday, September 8, 2010 2:24 AM
  • Hello!

    We are having this issue and are wondering if there are any solution/fix/workaround published yet. I cannot seem to access the issue that Fredrik has opened on Microsoft Connect.

    We've only worked with EF 4.0 with no prior experience, so we got this problem all over the place as soon as we started introducing the RowVersion in our entities to check for concurrency issues.

    Thanks in advance for any help!

    David

    Thursday, May 5, 2011 6:22 PM
  • Rest assured that we are working on addressing the issue.

    Faisal, what is the status of this issue? It has been quite a while since reported.

    Thanks again,

    Kris


    Kris I
    • Proposed as answer by Ravi Pathak Friday, November 25, 2011 9:22 AM
    • Unproposed as answer by Ravi Pathak Friday, November 25, 2011 9:22 AM
    Friday, July 15, 2011 4:58 PM
  • Hallo Faisal,

    Any chance of progress on removing this bug?

    My problem is also one of permissions on the referenced table (using SQL-Server 2008R2). One group of users is allowed to update the reference table which has a rowversion field, the other group are only allowed to select a value for a column in a referencing table. Again, as said elsewhere, EF shows no changes on the referenced table before saving but instead of just re-reading the rowversion from the referenced table after the update on the referencing table, it issues a "dummy" update on the referenced table which runs into a permissions exception.

    This "update" (like ...set @p=0...), which changes no columns in the referenced table, is unnecessary, the follow-up "select" is all that's required to refresh the rowversion (or calculated fields).

     

    Wednesday, October 19, 2011 1:06 PM
  • This is a known issue in Entity Framework 4.0 - the issue does not affect everyone as it is mostly limited to cases where you use ConcurrencyMode Fixed on principal end, in addition to Foreign Key associations that were introduced in .NET 4.0.

    Rest assured that we are working on addressing the issue.

    Thanks for sending us the feedback.

    Faisal Mohamood | Program Manager | Entity Framework

    Faisal,

    In my project, every entity inherits from a common base class (BaseEntity) which declares a RowVersion property. Therefore, this issue virtually impacts every entity that has a foreign key association. With multiple users working simultaneously, we get numerous DbUpdateConcurrencyException and there seems to be no feasible workaround to resolve that. :-(

    Could you please provide an update on the status of this issue or a workaround?

    Many Thanks

    Ravi


    Microsoft Certified Architect - Solutions
    Thursday, November 24, 2011 12:44 PM