Fixing 'Row not found or changed' Exception in LINQ to SQL on SQL Server Compact

Introduction

When working with LINQ to SQL on SQL Server Compact, you may encounter the "Row not found or changed" exception. This error occurs when you try to update or delete a row that has already been modified or deleted by another user or process. In this article, we will discuss how to fix this exception and prevent it from happening in the future.

Solution

To fix the "Row not found or changed" exception, you can use the following steps:

1. Refresh the data context: When you fetch data from the database using LINQ to SQL, the data is cached in memory by the data context. If another user or process modifies or deletes the same data, the cached data becomes stale and can cause the "Row not found or changed" exception. To fix this, you can refresh the data context by re-fetching the data from the database before updating or deleting the row.

using (var db = new MyDataContext())
{
    var row = db.MyTable.FirstOrDefault(x => x.Id == id);
    if (row != null)
    {
        db.Refresh(RefreshMode.OverwriteCurrentValues, row);
        db.MyTable.DeleteOnSubmit(row);
        db.SubmitChanges();
    }
}

2. Use optimistic concurrency: Optimistic concurrency is a technique that prevents the "Row not found or changed" exception by checking whether the row has been modified or deleted by another user or process before updating or deleting it. To use optimistic concurrency, you need to add a timestamp column to your table and set the UpdateCheck attribute of the column to UpdateCheck.Never.

[Table]
public class MyTable
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public byte[] Timestamp { get; set; }
}

When you update or delete a row, LINQ to SQL will check the timestamp column to see whether the row has been modified or deleted by another user or process. If the row has been modified or deleted, LINQ to SQL will throw a concurrency exception instead of the "Row not found or changed" exception.

3. Use transactions: Transactions are a powerful tool for preventing concurrency issues in LINQ to SQL. When you use transactions, you can ensure that a set of updates or deletes are executed atomically, meaning that they either all succeed or all fail. This prevents other users or processes from modifying or deleting the same data while you are working on it.

using (var db = new MyDataContext())
{
    using (var trans = new TransactionScope())
    {
        var row1 = db.MyTable.FirstOrDefault(x => x.Id == id1);
        var row2 = db.MyTable.FirstOrDefault(x => x.Id == id2);
        if (row1 != null && row2 != null)
        {
            db.MyTable.DeleteOnSubmit(row1);
            db.MyTable.DeleteOnSubmit(row2);
            db.SubmitChanges();
            trans.Complete();
        }
    }
}

In this example, we are deleting two rows in a single transaction. If another user or process tries to modify or delete the same data, they will have to wait until the transaction is complete before they can access the data.

Conclusion

The "Row not found or changed" exception is a common issue when working with LINQ to SQL on SQL Server Compact. However, by following the steps outlined in this article, you can prevent this exception and ensure that your database operations are executed smoothly and efficiently. Remember to refresh your data context, use optimistic concurrency, and use transactions to prevent concurrency issues and ensure the integrity of your data.