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

Question

I am trying to delete a selected gridview row using LINQ (No LINQDataSource). when the selection is changed, the detailsview binding is changed also. I can add a new entry to the database, but when i added this code to a delete button inside the updatePanel, i got an exception:

try
{

var query = from i in db.QuestionModules where i.QuestionModuleID == QuestionModuleID select i;
             QuestionModule o = query.First();
             db.QuestionModules.DeleteOnSubmit(o);
             db.SubmitChanges();
}.........

this is the exception i get:

System.Data.Linq.ChangeConflictException: Row not found or changed. at
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode
failureMode) at
System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges()

ive had this problem for about a week, and no matter what i do, its still there, and the record doesnt get deleted. any ideas on what to do?

Answer 1

Hi Matthew

I'm having the same problem, and came across this blog, which basically states that Linq-To-Sql has got a problem in it's optimistic concurrency where:

1. High precision datetime fields are used. The solution is to set UpdateCheck to never for that column your DBML file
2. GridView columns which are set to invisible are accessing a property on the data object (this second reason makes no sense, but it seems to be all the rage on that blog).

I haven't tried these solutions yet, but will post back here once I have.

Answer 2

OK - it looks as though (in my case at least) the answer was to set all non primary-key column's UpdateCheck property to Never in the DBML file. Doing this immediately cured the problem of "Row not found or changed".

Given the rumour that Microsoft are moth-balling Linq-To-Sql in favor of Entity Framework, one wonders whether these sorts of bugs will be fixed?

Answer 3

you have to add the following command under the ( QuestionModule o = query.First();) db.QuestionModule Attach(o); then your code

Answer 4

Ensure that there is no column with null value in the related table ie the table to be updated.

Answer 5

I was able to resolve this issue by executing databind() on the gridview and datasource during updatepanel postback.

    protected void UpdatePanel1_Load(object sender, EventArgs e)
    {
        GridView1.DataBind();
        LinqDataSource1.DataBind();
    }

I refresh updatepanel each time my selection index changes and it was able to resolve the conflicts.

Hope this helps.

Answer 6

Seemed to work for my situation also. I was building an in memory, never before submitted row which had several foreign key relationships to rows in other tables. The InsertOnSubmit seemed to work but a subsequent DeleteOnSubmit was giving me that row not found error. I was not populating every field in the row I submitted so I don't know if that had anything to do with it but marking all of the main table's non-primary key columns eliminated the error message.

One further thought: I take it that marking a column's UpdateCheck policy as 'Never' means that it is not used as the basis for optimistic concurency checking. This would imply that two users could write over a given row with different data in any such column and the conflicts would not be detected...meaning the last user to submit the row would overwrite the values of the previous users submission. I gathered from various online reading that one partial solution to this is to use the Refresh method immediately before submission to sync up any changes. Of course with no pesimistic lock on the row there is no guarantee that the row won't still be changed between the Refresh and the Submit but in most scenarios involving large databases this would be rare.

Update: On further review I think I have discovered a scenario that may be affecting others so I thought I would share it just in case. It turns out that at least part of the trouble I have been having with SQL LINQ is related to triggers. It appears that if you submit a row using SQL LINQ and your DBA has triggers designed to write information to some columns in that row then the SQL LINQ model will by default 'Always' determine that the row has been changed since your last write of it. I was submitting partially populated rows and our DBA's triggers are filling in some columns so that when I attempted to further modify the row in our code it sensed a change conflict based on the columns populated by the trigger. I am investigating now the best way to handle this but changing these trigger populated fields to use an UpdateCheck policy of 'When Changed' or 'Never' worked for me. Hope it helps.