none
System.Data.Linq.ChangeConflictException: Row not found or changed RRS feed

  • Question

  •  

    I'm trying to figure out why this is happening.

     

    I have an "Issues" table and a subtable "IssueActivities". We record the issue in Issues and activities (e.g. comments, resolution detail) in the IssueActivities table. Pretty simple.

     

    This is my unit test that fails:

     

    Code Snippet

    <TestMethod()> _

    Public Sub CommentIssue()

       'create a test issue

       Dim newissueID = CreateTempIssue()

       'create datacontext

       Dim db As New AnvilDBsecure

       'load test issue

       Dim newIssue = db.Issues.Single(Function(i) i.IssueID = newissueID)

     

       'add comment

       newIssue.Comment(db, "Test comment", False)

     

       'remove

       db.IssueActivities.DeleteAllOnSubmit(From ia In db.IssueActivities _

                  Where ia.IssueID  = newissueID)

       db.SubmitChanges()

       db.Issues.DeleteOnSubmit(newIssue) ' ERROR HERE

       db.SubmitChanges()

    End Sub

     

     

    The Comment method is very simple, it just creates an IssueActivity entry for the current issue..

     

    Code Snippet

    Public Sub Comment(ByRef db As AnvilDBsecure, _

                       ByVal comments As String, _

                       Optional ByVal sendToCustomer As Boolean = False)

       'Create new activity

       Dim activity = New IssueActivity

       With activity

          .CreatedBy = My.User.Name

          .CreatedDate = Now

          .IssueActivityTypeID = "Notes"

          .Note = comments

          .Issue = Me

       End With

       db.IssueActivities.InsertOnSubmit(activity)

     

       'Flag email sent?

       If sendToCustomer And HasEmail Then activity.CustomerEmailed = True

       'save BEFORE attempting email

       db.SubmitChanges()

       If sendToCustomer And HasEmail Then Me.SendCommentEmail(comments)

    End Sub

     

     

    So to recap, the test loads an issue, calls Comment method to add one entry to the subtable (successfully). It then tries to clean up and fails when it tries to delete the issue, with the exception


    "System.Data.Linq.ChangeConflictException: Row not found or changed".

     

    I've checked the two table definitions: both tables have all properties as UpdateCheck = Never since both have a timestamp column with "IsVersion=true".

     

    Wednesday, September 24, 2008 10:35 AM
    Answerer

Answers

  •  

    SOLUTION!

     

    I found that the IssueActivity table had an insert trigger on it which changes the ModifiedDate on the original Issue - hence this changes the timestamp on the Issue entry.

     

    So when LINQ to SQL attempts to delete with the "old" timestamp

     

    DELETE FROM [dbo].[Issues] WHERE ([IssueID] = @p0) AND ([ts] = @p1)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]

    This fails of course because the timestamp has modified the record.

    Thursday, September 25, 2008 7:27 AM
    Answerer

All replies

  • I've traced the SQL and this is what is being logged if it's any help...

     

    Code Snippet

     

    INSERT INTO [dbo].[Issues]([CustomerID], [Subject], [Note1], [Priority], [IssueCategoryID], [AssignedTo], [Status], [CreatedDate], [CreatedBy], [CustomerEmail], [ResolvedDate], [EstimatedResolveDate], [ExternalOrderNumber], [RemindMe], [RemindDate], [ModifiedDate], [ModifiedBy], [IssuesSubCategoryID], [XMLdatatype], [XMLdata])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19)

    SELECT [t0].[IssueID], [t0].[ts]
    FROM [dbo].[Issues] AS [t0]
    WHERE [t0].[IssueID] = (SCOPE_IDENTITY())
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p1: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Test]
    -- @p2: Input Text (Size = 5; Prec = 0; Scale = 0) [Notes]
    -- @p3: Input TinyInt (Size = 1; Prec = 0; Scale = 0) [3]
    -- @p4: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Other]
    -- @p5: Input VarChar (Size = 6; Prec = 0; Scale = 0) [Howard]
    -- @p6: Input Bit (Size = 0; Prec = 0; Scale = 0) [True]
    -- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [25/09/2008 08:15:55]
    -- @p8: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
    -- @p9: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
    -- @p10: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p11: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p12: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p13: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
    -- @p14: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p15: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p16: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p17: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Other]
    -- @p18: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p19: Input Xml (Size = 0; Prec = 0; Scale = 0) [System.Data.SqlTypes.SqlXml]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    INSERT INTO [dbo].[IssueActivities]([IssueID], [IssueActivityTypeID], [Note], [CreatedDate], [CreatedBy], [CustomerEmailed])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5)

    SELECT [t0].[IssueActivityID], [t0].[ts]
    FROM [dbo].[IssueActivities] AS [t0]
    WHERE [t0].[IssueActivityID] = (SCOPE_IDENTITY())
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Opened]
    -- @p2: Input Text (Size = 18; Prec = 0; Scale = 0) [Issue opened: Test]
    -- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [25/09/2008 08:15:55]
    -- @p4: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
    -- @p5: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[IssueID], [t0].[CustomerID], [t0].[Subject], [t0].[Note1], [t0].[Priority], [t0].[IssueCategoryID], [t0].[AssignedTo], [t0].[Status], [t0].[ts] AS [Ts], [t0].[CreatedDate], [t0].[CreatedBy], [t0].[CustomerEmail], [t0].[ResolvedDate], [t0].[EstimatedResolveDate], [t0].[ExternalOrderNumber], [t0].[RemindMe], [t0].[RemindDate], [t0].[ModifiedDate], [t0].[ModifiedBy], [t0].[IssuesSubCategoryID], [t0].[XMLdatatype], [t0].[XMLdata]
    FROM [dbo].[Issues] AS [t0]
    WHERE [t0].[IssueID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    INSERT INTO [dbo].[IssueActivities]([IssueID], [IssueActivityTypeID], [Note], [CreatedDate], [CreatedBy], [CustomerEmailed])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5)

    SELECT [t0].[IssueActivityID], [t0].[ts]
    FROM [dbo].[IssueActivities] AS [t0]
    WHERE [t0].[IssueActivityID] = (SCOPE_IDENTITY())
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Notes]
    -- @p2: Input Text (Size = 12; Prec = 0; Scale = 0) [Test comment]
    -- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [25/09/2008 08:15:56]
    -- @p4: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
    -- @p5: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[IssueActivityID], [t0].[IssueID], [t0].[IssueActivityTypeID], [t0].[Note], [t0].[ts] AS [Ts], [t0].[CreatedDate], [t0].[CreatedBy], [t0].[CustomerEmailed]
    FROM [dbo].[IssueActivities] AS [t0]
    WHERE [t0].[IssueID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    DELETE FROM [dbo].[IssueActivities] WHERE ([IssueActivityID] = @p0) AND ([ts] = @p1)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [109]
    -- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    DELETE FROM [dbo].[IssueActivities] WHERE ([IssueActivityID] = @p0) AND ([ts] = @p1)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [110]
    -- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    DELETE FROM [dbo].[Issues] WHERE ([IssueID] = @p0) AND ([ts] = @p1)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT NULL AS [EMPTY]
    FROM [dbo].[Issues] AS [t0]
    WHERE [t0].[IssueID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

     

     

    Thursday, September 25, 2008 7:20 AM
    Answerer
  •  

    SOLUTION!

     

    I found that the IssueActivity table had an insert trigger on it which changes the ModifiedDate on the original Issue - hence this changes the timestamp on the Issue entry.

     

    So when LINQ to SQL attempts to delete with the "old" timestamp

     

    DELETE FROM [dbo].[Issues] WHERE ([IssueID] = @p0) AND ([ts] = @p1)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [87]
    -- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]

    This fails of course because the timestamp has modified the record.

    Thursday, September 25, 2008 7:27 AM
    Answerer