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?