Linq-to-SQL Database Update Fails

Question

Very simple update. It simply fails, no error, no change gets made to the database.

Dim db As New BarClassesDataContext
Dim foo = (From a In db.articles Where a.id = 14 Select a).Single
Response.Write("<h3>" & foo.title & "</h3>")
foo.title = "This is my new, updated title for article ID #14"
db.SubmitChanges()

Here is the relevent portion of my article class. Also, this is a web form so I have no console. Is there another way to view the T-SQL output?

<Table(Name:="dbo.article")>  _
Partial Public Class article

    Private _id As Integer
    Private _issueid As Integer
    Private _dateadded As Date
    Private _title As String
    Private _titlelink As String
    Private _description As String
    Private _image As String
    Private _imagelink As String
    Private _type As Integer

    Public Sub New()
        MyBase.New
    End Sub

    <Column(Storage:="_id", AutoSync:=AutoSync.Always, DbType:="Int NOT NULL IDENTITY", IsDbGenerated:=true)>  _
    Public Property id() As Integer
        Get
            Return Me._id
        End Get
        Set
            If ((Me._id = value)  _
                        = false) Then
                Me._id = value
            End If
        End Set
    End Property

Answer 1

At first glance, I would say one (but not the main) problem is '='. I think it needs to be:

Dim foo = (From a In db.articles Where a.id == 14 Select a).Single

The main problem I see is the lack of an UpdatetOnSubmit() statement. How does L2S know you want to do an Update?

Try

db.Articles.UpdateOnSubmit(foo);
db.SubmitChanges(); 

or something close to this.

Answer 2

My suspicion is that you don't have a properly defined primary key in your database and/or in your System.Data.Linq.Mapping attributes. Show us the Article class - that will likely be where your problem is. Make sure you have an IDENTITY field in your database, and be sure your linqed up class has got IsPrimaryKey:=True and IsDbGenerated:=True in the <Column> attribute.

Also, it would be wise to set the .Log property on your DataContext to see what SQL is being executed. I like the debug window logger technique, which I have mentioned before here: http://stackoverflow.com/questions/3295703/datacontext-submitchanges-in-linq/3295750#3295750

Answer 3

If you have any invalid fields definitions you may have an issue where 0=1 is added to the WHERE clause. Check that all of your non-nullable fields are set. (I fought with this for about two hours one night while watching the SQL profiler add the extra 0=1 for no reason.)

• Post on Social MSDN

• Another post about this issue

(If I can find the issue on connect.microsoft.com I will post it as well)