views:

87

answers:

2

Hello,

I'm having some troubles with updating linq to sql entities. For some reason, I can update every single field of my item entity besides name.

Here are two simple tests I wrote:

 [TestMethod]
        public void TestUpdateName( ) {
            using ( var context = new SimoneDataContext( ) ) {
                Item item = context.Items.First( );

                if ( item != null ) {
                    item.Name = "My New Name";
                    context.SubmitChanges( );
                }
            }
        }

        [TestMethod]
        public void TestUpdateMPN( ) {
            using ( var context = new SimoneDataContext( ) ) {
                Item item = context.Items.First( );

                if ( item != null ) {
                    item.MPN = "My New MPN";
                    context.SubmitChanges( );
                }
            }
        }

Unfortunately, TestUpdateName() fails with the following error: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'..

And here's the outputted SQL:

UPDATE [dbo].[Items] SET WHERE ([Id] = @p0) AND ([CategoryId] = @p1) AND ([MPN] = @p2) AND ([Height] = @p3) AND ([Width] = @p4) AND ([Weight] = @p5) AND ([Length] = @p6) AND ([AdministrativeCost] = @p7) -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1] -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1] -- @p2: Input VarChar (Size = 10; Prec = 0; Scale = 0) [My New MPN] -- @p3: Input Decimal (Size = 0; Prec = 5; Scale = 3) [30.000] -- @p4: Input Decimal (Size = 0; Prec = 5; Scale = 3) [10.000] -- @p5: Input Decimal (Size = 0; Prec = 5; Scale = 3) [40.000] -- @p6: Input Decimal (Size = 0; Prec = 5; Scale = 3) [30.000] -- @p7: Input Money (Size = 0; Prec = 19; Scale = 4) [350.0000] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

As you can see, no update is being generated (SET is empty ...) I have no clue why is this happening.

And already in advance ... YES, the table Item has a PK (Id). Thank you in advance!

Update: It appears that the error is caused by overriding GetHashcode(). This is my current implementation:

return string.Format( "{0}|{1}|{2}|{3}", Name, Id, UPC, AdministrativeCost).GetHashCode( );

A: 

It appears that the SQL being generated is not including the contents of the SET clause (notice that there is no [Name] = @pXX after SET). Are all of the properties (data type, size, etc.) of the entity field set correctly in the dbml designer?

Adam Robinson
I paid attention to that and I edited the post a minute ago.The properties on the DBML seem to be correct.I use Hugati DBML tools to maintain my DBML schema in sync with the database.Thanks for the reply.
Isaac
@Isaac: The latest version still reflects this problem (it says `UPDATE [dbo].[Items] SET WHERE...`). L2S is not generating a list of columns to update and the values that go with them (you would expect it to say `UPDATE [dbo].[Items] SET [Name] = @p0 WHERE...` or something similar).
Adam Robinson
+2  A: 

It sounds like your DBML might be out of synch. You should delete the tables and re-add them and try and run it again.

Just delete your Items table manually and re-add it.

EDIT: Based on your edit, you should check out the following thread regarding GetHashCode.

http://social.msdn.microsoft.com/forums/en-US/linqtosql/thread/6cc6c226-f718-4b22-baad-dba709afe74b/

.Net rules claim that GetHashCode() and Equals() must always be implemented in tandem. Two objects that are equal must have the same hash code.

Also, 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.

Kelsey
Just deleted the table Manually and it did not work.I also tried again to sync it using Hugati tools, and I'm getting the same error.
Isaac
@Isaac Does `TestUpdateMPN` work and just `TestUpdateName` fail? This error seems to occur when the dbml definition of the table does not match the schema.
Kelsey