locked
Entity Framework Passing Null Timestamp/Rowversion RRS feed

  • Question

  • User-1858331913 posted

    I am getting an OptimisticConcurrencyException when attempting to delete a row.  The error said:

    Store update, insert, or delete statement affected an unexpected number of rows (0).
    Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

    I knew it wasn't a real concurrency problem, so I ran SQL Profiler to look at what it was doing.  This is the SQL code it generated for the delete.

    exec sp_executesql N'delete [dbo].[Forms]
    where (((([ServiceCode] = @0) and ([FormName] = @1)) and ([FiscalYearID] = @2)) and [VersionID] is null)',
    N'@0 varchar(10),@1 varchar(30),@2 int',@0='THECODE',@1='THEFORM',@2=8

    As you can see, the code automatically generated by the EntityDataSource control is passing a hard-coded null where it should be passing the VersionID (timestamp/rowversion). When it can't find such a row, it thinks the row was modified and throws the exception.

    In the EDM, VersionID has StoreGeneratedPattern=Computed, Concurrency=Fixed, and Nullable=False.

    I was able to get it working by adding VersionID to the ListView DataKeyNames field. However, I don't see why I should have to this, when I have other ListViews (pointing to other tables) where I don't have to add it. I tried removing the Include= on the control in case there was something in there causing a problem. It didn't help.

    How do I even begin to go about getting Entity Framework to generate correct SQL code in this case?

    For reference, the table is below.

    CREATE TABLE [dbo].[Forms](
    	[ServiceCode] [varchar](10) NOT NULL,
    	[FormName] [varchar](30) NOT NULL,
    	[FiscalYearID] [int] NOT NULL,
    	[LastChangeDate] [datetime2](2) NOT NULL,
    	[LastChangeBy] [varchar](64) NOT NULL,
    	[VersionID] [timestamp] NOT NULL,
     CONSTRAINT [PK_Forms] PRIMARY KEY CLUSTERED 
    (
    	[ServiceCode] ASC,
    	[FormName] ASC,
    	[FiscalYearID] ASC
    )
    )
    
    Wednesday, June 24, 2015 2:24 PM

Answers

  • User-84896714 posted

    Hi animedog,
    Thank you for your post.
    Did you execute the delete operate like this.

    var form = context.Forms.Where(s => s.FiscalYearID == 0 && s.FormName == "name1" && s.ServiceCode == "1").First();
    context.Forms.Remove(form);
    context.SaveChanges();

    Entity framework generate the sql for you, if the sql could not meet you, you could use below function.

    context.Database.ExecuteSqlCommand()

    Hoping my reply could be helpful to you.
    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 25, 2015 3:33 AM