Having trouble doing an Update with a Linq to Sql object
- by Pure.Krome
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?
