Having trouble doing an Update with a Linq to Sql object

Question

Hi folks,

i've got a simple linq to sql object. I grab it from the database and change a field then save.

No rows have been updated. :(

When I check the full Sql code that is sent over the wire, I notice that it does an update to the row, not via the primary key but on all the fields via the where clause. Is this normal? I would have thought that it would be easy to update the field(s) with the where clause linking on the Primary Key, instead of where'ing (is that a word :P) on each field.

here's the code...

using (MyDatabase db = new MyDatabase())
{
    var boardPost = (from bp in db.BoardPosts
        where bp.BoardPostId == boardPostId
        select bp).SingleOrDefault();

    if (boardPost != null &&
        boardPost.BoardPostId > 0)
    {
        boardPost.ListId = listId; // This changes the value from 0 to 'x'
        db.SubmitChanges();
    }
}

and here's some sample sql..

exec sp_executesql N'UPDATE [dbo].[BoardPost]
SET [ListId] = @p6
WHERE ([BoardPostId] = @p0) AND .... <snip the other fields>',N'@p0 int,@p1 int,@p2 nvarchar(9),@p3 nvarchar(10),@p4 int,@p5 datetime,@p6 int',@p0=1276,@p1=212787,@p2=N'ttreterte',@p3=N'ttreterte3',@p4=1,@p5='2009-09-25 12:32:12.7200000',@p6=72

Now, i know there's a datetime field in this update .. and when i checked the DB it's value was/is '2009-09-25 12:32:12.720' (less zero's, than above) .. so i'm not sure if that is messing up the where clause condition...

but still! should it do a where clause on the PK's .. if anything .. for speed!

Yes / no ?

---

UPDATE

After reading nitzmahone's reply, I then tried playing around with the optimistic concurrency on some values, and it still didn't work :(

So then I started some new stuff ... with the optimistic concurrency happening, it includes a where clause on the field it's trying to update. When that happens, it doesn't work.

so.. in the above sql, the where clause looks like this ...

WHERE ([BoardPostId] = @p0) AND ([ListId] IS NULL) AND ... <rest snipped>)

This doesn't sound right! the value in the DB is null, before i do the update. but when i add the ListId value to the where clause (or more to the point, when L2S add's it because of the optomistic concurrecy), it fails to find/match the row.

wtf?

Answer 1

The where clause stuff is normal- Google "optimistic concurrency" if you don't know why. You can opt out of the behavior by setting all the fields to "UpdateCheck:Never" in the dbml designer (but understand that you're giving up an important safety check by doing so).

The fact that the update fails is likely due to a mismatched datatype or source value between the DBML and the DB (it's easy for them to get out of sync- there are 3rd party tools to compare DBML to DB for this kind of thing). To test, try running the update statement you captured in SSMS but remove values from the where clause until you get "1 row affected". Once it works, you've found your problem column.

Answer 2

The behavior you are describing suggests that your database and your Linq to SQL object has gotten out of sync. You should try to delete the class from the Linq to SQL class designer, save, drag the table back into the designer from the Database Explorer, and save again. This should clear up the update problem.

Answer 3

try to put this before submitchanges()

_tdIssuesLog.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, issueslog);