Why can't I update data in a database using LINQ to SQL?

Question

I am trying to update the data while I am reading it from the database, see below. But after it was all over, the data was not updated.

Is there any transaction syntax I need to specify? (When I debug, I see that I have the correct entry.)

using (conn = new SqlConnection(MyConnectionString)) using (SqlCommand cmd = new SqlCommand("dbo.MyProcedure", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Count", count); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { // wrapper object, not related to database SampleModel c = new SampleModel(); c.ID= (string)reader["ID"]; c.Name = (string)reader["Name"]; c.Type = (int)reader["Type"]; // modeList will return to outside, not related to database modelList.Add(c); sampleTable1 table1 = context.sampleTable1s.SingleOrDefault(t=> t.id = c.ID); // try to update the "isRead", but it doesn`t work....!!! // about the datatype, in the data base, it is "smallInt" // in linq to sql, it is "short?" // PS Default value all should be 0 table1.isRead = 1; context.SubmitChanges(); <--- here, it doesn`t do the job // context is new from Linq to SQL } } conn.Close(); } 

Here is my procedure:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE MyProcedure @Count int = 100 AS BEGIN SELECT TOP (@Count ) t1.id AS ID, t1.name AS Name, t2.type AS TYPE FROM sampleTable1 as t1 with (nolock), sampleTable2 as t2 with (nolock) WHERE (t1.t2Id = t2.Id) ORDER BY t1.name asc END GO 

And if I put all my code inside a TransactionScope block

 using (TransactionScope scope = new TransactionScope()) { // all the C# code above scope.Complete(); } 

I will get an exception. MSDTC on server localhost-sqlserver2005 is not available.

And if I just put part of the code, there are no exceptions, but the data was not updated

 using (TransactionScope scope = new TransactionScope()) { sampleTable1 table1 = context.sampleTable1s.SingleOrDefault(t=> t.id = c.ID); table1.isRead = 1; context.SubmitChanges(); scope.Complete(); } 

Thanks.

Answer 1

Make sure that at least one member in the entity class in question is marked as a member of the primary key in the L2S designer. If the subject does not have any members of the PC, L2S will silently ignore it when sending updates (silently, as in no case, it will not be generated and sent to db).

Answer 2

Make sure you have the primary key for this table. If you cannot do this in db, do it in the linq2sql designer.

Answered this one here , here and here . Also mentioned are linq to sql traps .