CODEWITHSUNDEEP
c#.netlinq
San
San

Reputation: 691

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

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()

I've had this problem for about a week, and no matter what I do, it is still there, and the record doesn't get deleted.

Any ideas on what to do?

Upvotes: 69

Views: 64956

Answers (19)

Greg Z.
Greg Z.

Reputation: 1566

For us the problem started when we switched to DateTime2 on the SQL Server side. Just marking fields with Column(DbType = "DateTime2") did not help. So, what happened was that initially on the database side we declared our columns as DateTime2(3) to be "backward compatible" with the old DateTime type and everything seemed to work fine until we noticed that when we use SQL-2-Linq we get the "Row not found or changed" exception in the updates of those date fields. To make the quite-long story short the solution was to do 2 things:

  1. Mark the columns with [Column(DbType = "DateTime2(3)", CanBeNull = false)] to match the database declaration. AND
  2. Trim the extra precision digits from the properties in setters like so:

[Column(DbType = "DateTime2(3)", CanBeNull = false)] public DateTime ModifiedAt { get => _modifiedAt; set => _modifiedAt = value.AddTicks(-(value.Ticks % TimeSpan.TicksPerMillisecond)); }

Upvotes: 0

John Pasquet
John Pasquet

Reputation: 1842

As @dherrin79 indicated, this can be caused because of a precision variance between the database and the code. For me, the issue was the database column was supposed to be decimal(10,2), but it had been created as decimal(18,0). This was for a money field, so perhaps I should have used the money column type.

So, I saved a dollar amount, like $3.14 to it, but the decimal was stripped. This resulted in the database value being changed and not matching the value in C#.

Hope this helps.

Upvotes: 0

Nate S.
Nate S.

Reputation: 1157

This is just a case of mismatched column definitions. Just remove the table from .dbmland re-add. Make sure to change auto generate value property = true for columns that have auto generated data such as Primary Keys or datetime columns.

Upvotes: 3

dherrin79
dherrin79

Reputation: 377

The problem I had was that I had a DateTime type in the .net framework, but our database field was of DateTime2 type, which is higher precision datatype. So when we would submit changes the date fields of the object vs. the DB was just a few nanoseconds off which would cause the concurrency error. This happened when we migrated to a newer MSSQL version and it converted our DateTime fields to DateTime2.

So in our code where we had:

    Obj.DateUpdated = DateTime.Now()

We changed it to:

    Obj.DateUpdated = DateTime.Parse(DateTime.Now.ToString())

So check your datatypes, especially your date fields, if you get this error after making an upgrade and or migration.

Upvotes: 2

FranticRock
FranticRock

Reputation: 3283

I got around this issue, by making sure to refresh my object immediately before updating it. I do this with the KeepChanges option.

    db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, employee);

Upvotes: 23

saltyobi
saltyobi

Reputation: 19

Ensure that no columns contain null values in the related table (i.e. the table to be updated).

Upvotes: 1

AJ17
AJ17

Reputation: 308

The way i fixed it was: First I update the database, then I set the new values for the grid as

e.Keys["ColumnOne"] ="new value"
e.Keys["ColumnTwo"] ="new value"

All this was done under the GridView_RowUpdating event.

Upvotes: 0

tec-goblin
tec-goblin

Reputation: 316

For me it was an enum column (mapped to a varchar) which caused the problem, so I had to pass update check to never.

Upvotes: 0

Jason
Jason

Reputation: 70

I had a similar problem and although deleting and re-adding the DBML table/class helped some users, for me it was a bit different since I'm using WCF with detached entity and a ListView on the client.

If I used the .Attach(entity) it failed - "Row not found or changed" But when using .Attach(entity, original) it works every time

public void DeleteTask(Task task)
    {
        TwoDooDataContext db = new TwoDooDataContext();
        db.Tasks.Attach(task,GetTaskByID(task.ID));
        db.Tasks.DeleteOnSubmit(task);
        db.SubmitChanges();
    }

Upvotes: 0

Jason Butera
Jason Butera

Reputation: 2440

I just wanted to add my scenario for anybody who may have this issue.

We use a custom T4 against our Linq to SQL dbml. We basically just modified the original get/set of string properties to automatically trim and set null.

        get { return _OfficiantNameMiddle.GetValueOrNull(); }
        set 
        {
            value = value.GetValueOrNull();
            if (_OfficiantNameMiddle != value) 
            {
                _IsDirty = true;
                OnOfficiantNameMiddleChanging(value);
                SendPropertyChanging("OfficiantNameMiddle");
                _OfficiantNameMiddle = value;
                SendPropertyChanged("OfficiantNameMiddle");
                OnOfficiantNameMiddleChanged();
            }
        }

Legacy data in our database had some leading/trailing spaces so any concurrency check on these columns failed to result in a match (it was comparing the trimmed value against the non-trimmed database value). It was really easy to profile SQL, grab the SQL and start commenting out the items in the WHERE clause until it started returning a row during the concurrency check.

Luckily, we have a LastUpdatedOn field in our tables that is automatically set via OnValidate(System.Data.Linq.ChangeAction).

    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        if (action == System.Data.Linq.ChangeAction.Insert)
        {
            CreatedBy = CurrentUserID;
            CreatedOn = DateTime.Now;
            LastUpdatedBy = CreatedBy;
            LastUpdatedOn = CreatedOn;
        }
        else if (action == System.Data.Linq.ChangeAction.Update)
        {
            LastUpdatedBy = CurrentUserID;
            LastUpdatedOn = DateTime.Now;
        }
    }

In order to bypass the problem, we just set the concurrency check to Never on all columns except the Primary Key columns and LastUpdatedOn column. This worked for us.

Upvotes: 0

localman
localman

Reputation: 497

Also - if you're calling the selecting method for the linqdatasource and setting e.result manually, make sure you're including any foreignkey values as well.

Nothing else worked for me but this.

Upvotes: 0

suhad
suhad

Reputation: 1

Under:

QuestionModule o = query.First();

You have to add the following command:

db.QuestionModule.Attach(o);

Upvotes: 0

SemMike
SemMike

Reputation: 1453

You are getting this error quite possibly because one of your fields has something different in the Linq To SQL designer and in the actual database.

In my case, it was because one of the fields was nullable in the database and not nullable in the designer, making it nullable in the designer as well solved the problem immediately.

Upvotes: 64

Fayssal El Moufatich
Fayssal El Moufatich

Reputation: 169

The problem could be also simply that the DBML definition of the table is not consistent with the status of the Database definition. I just removed the DBML model and inserted it again from the database, and it worked.

Hope this helps somebody.

Upvotes: 13

Michael9000
Michael9000

Reputation: 21

I had similar changeconflictexception/"Row not found or changed" when updating a row. Solved it by re-adding the tabbles in the dbml.

Upvotes: 2

Mark
Mark

Reputation: 9428

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?

Upvotes: 72

Travis
Travis

Reputation: 572

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.

Upvotes: 6

Darwin N.
Darwin N.

Reputation: 1

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.

Upvotes: 0

Mark
Mark

Reputation: 9428

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.

Upvotes: 15

Related Questions