none
Inclusion of a timestamp field in a class causes "Cannot update a timestamp column" Exception RRS feed

  • Question

  • I have an SQL table called Products. In order to handle concurrency, I added a timestamp column "RowVersion". 

    The table definition is as follows:

    CREATE TABLE [dbo].[Products] (
        [ProductID]   INT             IDENTITY (1, 1) NOT NULL,
        [Name]        NVARCHAR (100)  NOT NULL,
        [Description] NVARCHAR (500)  NOT NULL,
        [Category]    NVARCHAR (50)   NOT NULL,
        [Price]       DECIMAL (16, 2) NOT NULL,
        [RowVersion]  ROWVERSION      NOT NULL,
        PRIMARY KEY CLUSTERED ([ProductID] ASC)
    );

    In the corresponding class "Product", I also added a field for the timestamp, it is also called "RowVersion":

        public partial class Product
        {
            public int ProductID { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public string Category { get; set; }
            public decimal Price { get; set; }
            public byte[] RowVersion { get; set; }
        }

    However, when calling "context.SaveChanges()", it gives "Cannot update a timestamp column" Exception. Below is a snippet of the code:

                    Product dbProduct = context.Products.Where(s => s.ProductID == product.ProductID).Single();
                    if (dbProduct != null) {
                        ... update product fields here ...
                    }

                    context.Configuration.ProxyCreationEnabled = false;
                    context.Entry(dbProduct).State = EntityState.Modified;
                    context.SaveChanges();

    Without the timestamp field in the class, the Exception will not occur.

    Questions:

    (1) A timestamp column is automatically managed (updated when data is changed) by the DB. Why should it be included in the corresponding class ?

    (2) But, if it is eliminated from the class (from the .edmx file), how can we set its Concurrency Mode to "Fixed" (by right clicking the timestamp field in the .edmx file and click "properties").

    Thanks so much in advance,


    Goldstar Lee

    Saturday, July 9, 2016 4:32 AM

Answers

  • Hi Goldstar Lee,

    I have downloaded your code. I find that you have a EMDX file and DBContext. it seems that your app use database first approach and code first approach. I would suggest a project only one approach.

    If you use code first approach and use timestamp to handle concurrency. we need to add a timestamp attribute above the property. like this:

    public class Product
        {
            public int ProductID { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public string Category { get; set; }
    
            /* Note 1:
             * If this column (ie, RowVersion) is deleted (commented out), then whether "context.Entry(dbProduct).State = EntityState.Modified" is there or not,
             * (See Note 2 in Repository.cs), the EXCEPTION will NOT occur.
    
      Please add Timestamp attribute here 
             */
            [Timestamp]
            public byte[] RowVersion { get; set; }
    
            public int UsageStamp { get; set; }
        }

    For more information, please refer to:

    http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

    If you use Database first approach, please set the concurrency mode to fixed by right clicking on RowVersion property in the Product entity, For more information, please refer to:

    http://www.entityframeworktutorial.net/EntityFramework5/handle-concurrency-in-entity-framework.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Goldstar Lee Thursday, July 12, 2018 3:06 AM
    Thursday, July 21, 2016 8:47 AM
    Moderator

All replies

  • Hi Goldstar Lee,

    According to your description, I create a database first demo with entity framework 6.1.3 and .net framework4.5. it works fine.

    >>Why should it be included in the corresponding class?

    Entity framework map all the fields to entities class. (such as identity field).

    >>if it is eliminated from the class (from the .edmx file), how can we set its Concurrency Mode to "Fixed" (by right clicking the timestamp field in the .edmx file and click "properties").

    As far as I know, entity framework need to have a rowversion column in the table in order to handle concurrency with related entity.

    For more information about Concurrency in Entity Framework, please refer to:

    http://www.entityframeworktutorial.net/EntityFramework5/handle-concurrency-in-entity-framework.aspx

    #Code First approach

    http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 11, 2016 2:37 AM
    Moderator
  • Hi Cole Wu,

    (1) I realized that RowVersion is automatically mapped by the EntityFramework. My question is, since I read it somewhere that RowVersion is automatically managed by the DB, then it should be transparent to the application (ie, the application does not need to know about it). What is the purpose that it is added to the class (by the EntityFramework) ?

    (2) Allso, the problem is, if RowVersion is in the class, then "Cannot update a timestamp column" exception will occur whenever a table row is updated, as stated above.

    I could put my code into OneDrive so that you could reproduce the above exception, if you wish.

    Really appreciate,


    Goldstar Lee



    Monday, July 11, 2016 3:35 PM
  • Hi Goldstar Lee,

    >>What is the purpose that it is added to the class (by the EntityFramework) ?

    Such as, we could retrieve the value from database. If you have any good suggestions, I would suggest that you could post your suggestion on the following page. maybe it will update on next version.

    https://visualstudio.uservoice.com/forums/121579-visual-studio-2015

    >>Allso, the problem is, if RowVersion is in the class, then "Cannot update a timestamp column" exception will occur whenever a table row is updated, as stated above.

    it seems that we don't update rowversion field on entity framemwork code.

    >>I could put my code into OneDrive so that you could reproduce the above exception

    If possible, please share your project to us via OneDrive, I'll reproduce your issue on my side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 12, 2016 8:59 AM
    Moderator
  • Hi Cole Wu,

    I will put a copy of my project in OneDrive so that you can look at it and/or reproduce the problem ("Cannot update a timestamp column" exception) in my spare time later tonight.

    Thanks so much,


    Goldstar Lee

    Tuesday, July 12, 2016 4:01 PM
  • Hi Cole Wu,

    Here is the link to my code in my OneDrive: https://1drv.ms/f/s!Ao3f50fVp-bxjRUQom7fYmdt1nQ6

    You can run the Product.aspx with the Visual Studio.

    I put in two notes: one in Product.cs (stating that if RowVersion is commented out, then Exception won't occur; another one in Repository.cs (stating that if "context.Entry(dbProduct).State = EntityState.Modified;" is commented out, the Exception won't occur either).

    The problem occurs only when "Update"ing a row. "Delete" and "Insert" are OK.

    Thanks again,


    Goldstar Lee


    Tuesday, July 12, 2016 9:12 PM
  • Hi Goldstar Lee,

    I have downloaded your code. I find that you have a EMDX file and DBContext. it seems that your app use database first approach and code first approach. I would suggest a project only one approach.

    If you use code first approach and use timestamp to handle concurrency. we need to add a timestamp attribute above the property. like this:

    public class Product
        {
            public int ProductID { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public string Category { get; set; }
    
            /* Note 1:
             * If this column (ie, RowVersion) is deleted (commented out), then whether "context.Entry(dbProduct).State = EntityState.Modified" is there or not,
             * (See Note 2 in Repository.cs), the EXCEPTION will NOT occur.
    
      Please add Timestamp attribute here 
             */
            [Timestamp]
            public byte[] RowVersion { get; set; }
    
            public int UsageStamp { get; set; }
        }

    For more information, please refer to:

    http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

    If you use Database first approach, please set the concurrency mode to fixed by right clicking on RowVersion property in the Product entity, For more information, please refer to:

    http://www.entityframeworktutorial.net/EntityFramework5/handle-concurrency-in-entity-framework.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Goldstar Lee Thursday, July 12, 2018 3:06 AM
    Thursday, July 21, 2016 8:47 AM
    Moderator
  • Hi Cole Wu,

    Thanks for your excellent answer (put [Timestamp] on top of the RowVersion). It works. You answered this about 2 years ago, but I didn't read it until today because I had switched direction using the other approach before I got your answer. Actually, I stumbled into your answer while google-searching for an unrelated problem, and the topic looked familiar to me.

    Also thanks for suggesting not using DB First and Code First approaches simultaneously. In that case I would opt for DB first. But then instead of using code first, what should I use ? SqlDataSource or ObjectDataSource ? 

    My apology and thanks again,


    Goldstar Lee

    Thursday, July 12, 2018 3:21 AM