CODEWITHSUNDEEP
c#linq-to-sql
Andy Link
Andy Link

Reputation: 139

Updating a record using Linq-to-SQL

I do my query...

var result = from u in tdc.tblUsers
             where u.UserID == userID
             select u;

and then I change the values I want to:

foreach (tblUsers u in result)
{
     //change values (and no im not changing the primary key or foreign keys)
}

then I submit changes

tdc.SubmitChanges();

When it hits submit changes, it throws exception that the row wasn't found or was changed. I am the only person using this so there's no other conflicts with accessing the db or locking. Why would it throw the ChangeConflictException? I have stepped through with debugger and the data persists all the way through the process, including the changes that I'm trying to make.

I have also tried it this way before and got the same error

tblUsers result = (from u in tdc.tblUsers
                   where u.UserID == userID
                   select u).Single();
result.Address = address;
result.Phone = phone;
tdc.SubmitChanges();

It will only ever retrieve 1 record with this query since UserID is the primary key.

I've done this many times and it has worked. Every example I've found is exactly what I have.

Upvotes: 9

Views: 42354

Answers (2)

Sascha
Sascha

Reputation: 601

Maybe you work with different context? Try to encapsulate it by using

using (myContext ctx = new myContext())
{
    var user = ctx.users.first();  
    user.name="blah";
    ctx.SubmitChanges();
}

Upvotes: 4

EricRRichards
EricRRichards

Reputation: 474

Quite often, if you look at the actual SQL that Linq-to-SQL generates, you'll see that it is being really over-zealous with matching the database row that you initially retrieved. Imagine that you have a table with columns ID(PK), A, B, C. You would think that if you updated column C for a row, it should be sufficient to update the row with a matching primary key. But what often happens is that Linq-to-SQL is also trying to match on columns A and B as well. Normally, that's fine. Unless you have concurrent writes, either from multi-threading or multi-processes, and something else modifies column A or B for the record that you are trying to update. Then you get these System.Data.Linq.ChangeConflictException: Row not found or changed. errors when you call SubmitChanges() on your data context.

Upvotes: 0

Related Questions