Linq2Sql error in generated SQL query for update – SqlException: Incorrect syntax near the keyword ‘WHERE’.

Get that error while calling DataContext.SubmitChanges()?

That’s because the SET clause of the generated SQL statement for the update is empty, like this:

UPDATE [dbo].[Food]

SET

WHERE ([id] = @p0) AND ([Name] = @p1) AND ([Kcal] = @p2) AND ([Carbs] = @p3) AND ([Fat] = @p4) AND ([Protein] = @p5) AND ([Creator] = @p6)

-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [205]

-- @p1: Input NVarChar (Size = 20; Prec = 0; Scale = 0) [UR Fettes Schnitzale]

-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [600]

-- @p3: Input Float (Size = 0; Prec = 0; Scale = 0) [40]

-- @p4: Input Float (Size = 0; Prec = 0; Scale = 0) [99]

-- @p5: Input Float (Size = 0; Prec = 0; Scale = 0) [0,150000005960464]

-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [240]

And that’s because… Linq2Sql’s DataContext’s entity identity tracking gets very confused when you override GetHashcode() and Equals() methods in your Entity Classes. Seriously. <sarcasm>But why would you want to do that anyway?</sarcasm>

Quoting from here:

“… the combination of GetHashCode() + Equals() forms the entity’s concept of identity.  If you make it based on field values (other than PK) then the identity changes as you change the fields. This is bad if L2S must lookup other info in a dictionary based on the entity’s identity, and especially if L2S needs to find an entity in its identity cache!

Advice: don’t change the identity of an entity.  L2S expects it to be based on the object’s natural (address based) identity.“