LinqToSQL Not Updating the Database...

Question

// goal: update Address record identified by "id", with new data in "colVal"

string cstr = ConnectionApi.GetSqlConnectionString("SwDb"); // get connection str
using (DataContext db = new DataContext(cstr)) {
    Address addr = (from a in db.GetTable<Address>()
                    where a.Id == id
                    select a).Single<Address>();
    addr.AddressLine1 = colValue.Trim();
    db.SubmitChanges(); // this seems to have no effect!!!
}

In the debugger, addr has all the current values from the db table, and I can verify that AddressLine1 is changed just before I call db.SubmitChanges()... SQL Profiler shows only a "reset connection" when the SubmitChanges line executes. Anyone got a clue why this isn't working? THANKS!

Answer 1

You can get a quick view of the changes to be submitted using the GetChangeSet method.

Also make sure that your table has a primary key defined and that the mapping knows about this primary key. Otherwise you won't be able to perform updates.

Answer 2

Funny, to use GetTable and Single. I would have expected the code to look like this:

string cstr = ConnectionApi.GetSqlConnectionString("SwDb"); // get connection str
using (DataContext db = new DataContext(cstr)) 
{    
  Address addr = (from a in db.Address where a.Id == id select a).Single();    
  addr.AddressLine1 = colValue.Trim();    
  db.SubmitChanges(); // this seems to have no effect!!!
}

I got no idea what GetTable will do to you.

Another thing, for debugging Linq2SQL try adding

db.Log = Console.Out;

before SubmitChanges(), this will show you the executed SQL.

Answer 3

Thanks -- your comments will help me sort this out I'm sure! I didn't have the "Id" column defined as the PrimaryKey so that's an obvious non-starter. I would have expected that LinqToSQL would have thrown an error when the update fails. -- S.

Answer 4

Ok, here's the result. I can't use the form db.Address, because I didn't use the designer to create my database objects, instead I defined them as classes like this:

[Table(Name = "Addresses")]
public class Address
{
    [Column(Name = "Id",IsPrimaryKey=true)]
     public int Id { get; set; }
    [Column(Name = "AddressLine1")]
    public string AddressLine1 { get; set; }
        ...

Originally, I didn't have the "Id" column set as PK in the database, nor did I have it identified using IsPrimaryKey=true in the [Column...] specifier above. BOTH are required! Once I made that change, the ChangeSet found the update I wanted to do, and did it, but before that it told me that 0 rows needed to be updated and refused to commit the changes.

Thanks for your help! -- S.