What can I do to resolve a "Row not found or changed" Exception in LINQ to SQL on a SQL Server Compact Edition Database?

Kevin_2723 0
Viewed 72562

When executing SubmitChanges to the DataContext after updating a couple properties with a LINQ to SQL connection (against SQL Server Compact Edition) I get a "Row not found or changed." ChangeConflictException.

var ctx = new Data.MobileServerDataDataContext(Common.DatabasePath);
var deviceSessionRecord = ctx.Sessions.First(sess => sess.SessionRecId == args.DeviceSessionId);

deviceSessionRecord.IsActive = false;
deviceSessionRecord.Disconnected = DateTime.Now;

ctx.SubmitChanges();

The query generates the following SQL:

UPDATE [Sessions]
SET [Is_Active] = @p0, [Disconnected] = @p1
WHERE 0 = 1
-- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:12:02 PM]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

The obvious problem is the WHERE 0=1, After the record was loaded, I've confirmed that all the properties in the "deviceSessionRecord" are correct to include the primary key. Also when catching the "ChangeConflictException" there is no additional information about why this failed. I've also confirmed that this exception get's thrown with exactly one record in the database (the record I'm attempting to update)

What's strange is that I have a very similar update statement in a different section of code and it generates the following SQL and does indeed update my SQL Server Compact Edition database.

UPDATE [Sessions]
SET [Is_Active] = @p4, [Disconnected] = @p5
WHERE ([Session_RecId] = @p0) AND ([App_RecId] = @p1) AND ([Is_Active] = 1) AND ([Established] = @p2) AND ([Disconnected] IS NULL) AND ([Member_Id] IS NULL) AND ([Company_Id] IS NULL) AND ([Site] IS NULL) AND (NOT ([Is_Device] = 1)) AND ([Machine_Name] = @p3)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [0fbbee53-cf4c-4643-9045-e0a284ad131b]
-- @p1: Input Guid (Size = 0; Prec = 0; Scale = 0) [7a174954-dd18-406e-833d-8da650207d3d]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:20:50 PM]
-- @p3: Input String (Size = 0; Prec = 0; Scale = 0) [CWMOBILEDEV]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p5: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:20:52 PM]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

I have confirmed that the proper primary fields values have been identified in both the Database Schema and the DBML that generates the LINQ classes.

I guess this is almost a two part question:

  1. Why is the exception being thrown?
  2. After reviewing the second set of generated SQL, it seems like for detecting conflicts it would be nice to check all the fields, but I imagine this would be fairly inefficient. Is this the way this always works? Is there a setting to just check the primary key?

I've been fighting with this for the past two hours so any help would be appreciated.

linq-to-sql linq .net
16 Answers

Thats nasty, but simple:

Check if the data types for all fields in the O/R-Designer match the data types in your SQL table. Double check for nullable! A column should be either nullable in both the O/R-Designer and SQL, or not nullable in both.

For example, a NVARCHAR column "title" is marked as NULLable in your database, and contains the value NULL. Even though the column is marked as NOT NULLable in your O/R-Mapping, LINQ will load it successfully and set the column-String to null.

  • Now you change something and call SubmitChanges().
  • LINQ will generate a SQL query containing "WHERE [title] IS NULL", to make sure the title has not been changed by someone else.
  • LINQ looks up the properties of [title] in the mapping.
  • LINQ will find [title] NOT NULLable.
  • Since [title] is NOT NULLable, by logic it never could be NULL!
  • So, optimizing the query, LINQ replaces it with "where 0 = 1", the SQL equivalent of "never".

The same symptom will appear when the data types of a field does not match the data type in SQL, or if fields are missing, since LINQ will not be able to make sure the SQL data has not changed since reading the data.

Sam_70210

I solved this error by redragging over a table from the server explorer to the designer and re-building.

Admin1

I don't know if you've found any satisfactory answers to your question, but I posted a similar question and eventually answered it myself. It turned out that the NOCOUNT default connection option was turned on for the database, which caused a ChangeConflictException for every update made with Linq to Sql. You can refer to my post at here.

user_14567540

In my case, the error was raised when two users having different LINQ-to-SQL data contexts updated the same entity in the same way. When the second user attempted the update, the copy they had in their data context was stale even though it was read after the first update had completed.

I discovered the explanation and solution in this article by Akshay Phadke: https://www.c-sharpcorner.com/article/overview-of-concurrency-in-linq-to-sql/

Here's the code I mostly lifted:

try
{
    this.DC.SubmitChanges();
}
catch (ChangeConflictException)
{
     this.DC.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

     foreach (ObjectChangeConflict objectChangeConflict in this.DC.ChangeConflicts)
     {
         foreach (MemberChangeConflict memberChangeConflict in objectChangeConflict.MemberConflicts)
         {
             Debug.WriteLine("Property Name = " + memberChangeConflict.Member.Name);
             Debug.WriteLine("Current Value = " + memberChangeConflict.CurrentValue.ToString());
             Debug.WriteLine("Original Value = " + memberChangeConflict.OriginalValue.ToString());
             Debug.WriteLine("Database Value = " + memberChangeConflict.DatabaseValue.ToString());
         }
     }
     this.DC.SubmitChanges();
     this.DC.Refresh(RefreshMode.OverwriteCurrentValues, att);
 }

When I looked at my output window while debugging, I could see that the Current Value matched the Database Value. The "Original Value" was always the culprit. That was the value read by the data context before applying the update.

Thanks to MarceloBarbosa for the inspiration.

user_7739060

After employing qub1n's answer, I found that the issue for me was that I had inadvertently declared a database column to be decimal(18,0). I was assigning a decimal value, but the database was changing it, stripping the decimal portion. This resulted in the row changed issue.

Just adding this if anyone else runs into a similar issue.

user_9260530

I know this is an older post, but the issue can still be problematic today. I wanted to share my experience with this; as the solution for me was slightly different than the accepted answer. The accepted answer however did lead me to resolve my issue so thank you!

In my case, I had an update trigger that would auto-insert a row into a status history table anytime the status changed on a row in a table (SQL Server); based on a set of known codes. My history table had a NOT NULL attribute for the status ID column, and my INSERT statement didn't take into account that a previously unknown code might slip through; thereby causing the row to insert to fail.

So the moral of the story is in addition to checking your data models, be sure to review any triggers you have defined as that too will result in a "row not found or changed" error.

Hope this helps someone else down the line; thanks all!

user_56274880

I had the same problem when inserting data and then wanting to modify or delete them in the same form, the solution I found to this was the following:

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

db = is your connection variable as you might imagine, and employee would be the variable you would be using for your table.

user_188655180
Related