none
Updating with entity class containing overridden GetHashCode() throws exception "Incorrect syntax near the keyword 'WHERE'."

    Question

  • Hi,

     

    I've used SQLMetal to generate a DataContext as well as an external mapping file for use with my existing business entities and Insert, Retrieve and Delete are working fine.

     

    When I try to update, however, an exception is thrown with the message "Incorrect syntax near the keyword 'WHERE'."

     

    Looking at the SQL statement that's actually sent to SQL Server (through SQL Server Profiler) shows that the statement is missing the fields to be updated in the SET clause, even though the fields that the concurrency update check uses in the where clause are all there. i.e.

     

    Code Snippet
    UPDATE [dbo].[People]
    SET
    WHERE ([PersonID] = @p0) AND ([FirstName] = @p1) AND ([LastName] = @p2)',N'@p0 int,@p1 varchar(4),@p2 varchar(4)',@p0=1,@p1='Test',@p2='User'

     

     

    I've tried the updates using two different methods. First by retrieving the entity from the database and then updating it within the same DataContext like so:

     

    Code Snippet

    Person person = new Person { FirstName = "New", LastName = "New", PersonID = 1 };

     

    using (PeopleDataContext dc = new PeopleDataContext(connString, PeopleMappingSource))

    {

    Person dbPerson = dc.People

    .Where(p => p.PersonID == person.PersonID)

    .SingleOrDefault();

     

    dbPerson.FirstName = person.FirstName;

    dbPerson.LastName = person.LastName;

     

    dc.SubmitChanges();

    }

     

     

     

    'PeopleMappingSource' is the embedded mapping file. I've also tried it by attaching the Person to a DataContext like so:

     

     

    Code Snippet

    Person person = new Person { FirstName = "New", LastName = "New", PersonID = 1 };

     

    //get Person from db with ID of 1 using different DataContext

    Person dbPerson = GetPerson(1); 

     

    using (PeopleDataContext dc = new PeopleDataContext(connString, PeopleMappingSource))

    {

    dc.People.Attach(dbPerson);

     

    dbPerson.FirstName = person.FirstName;

    dbPerson.LastName = person.LastName;

     

    dc.SubmitChanges();

    }

     

     

    Both throw the same exception. Once the overridden GetHashCode() method is removed from the Person class however, the updates are successful.

     

    I've tried this both with and without the addtional change tracking code (INotifyPropertyChanging, INotifyPropertyChanged, etc) in the Person class with the same results.

     

    Additionally, for the sake of my own sanity, I created a separate DBML file using Visual Studio rather than SQLMetal, and had the Person entity class generated automatically by dropping the People table from the database onto the designer.

     

    I then overrode the GetHashCode() in that generated Person class, used that newly generated DataContext to update and was again faced with same exception.

     

    The GetHashCode() in the Person class looks like this:

     

    Code Snippet

    public override int GetHashCode()

    {

    return FirstName.GetHashCode() ^ LastName.GetHashCode();

    }

     

     

    Has anyone else come across this? I'm presuming it's a bug of some sort, as I'd expect some other sort of exception to be thrown before an incomplete SQL statement like that is sent to SQL Server.

     

    Any help would be much appreciated!

     

    Thanks in advance.

    Friday, June 13, 2008 5:16 AM

Answers

  • .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.

     

     

     

    Friday, June 13, 2008 4:16 PM

All replies

  • .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.

     

     

     

    Friday, June 13, 2008 4:16 PM
  •  

    Thanks for the quick reply Matt.

     

    I forgot to mention that Equals is of course is overriden as well. It is also based on field equality.

     

    After debugging, looking through stack traces and reading numerous articles about the DataContext's identity tracking, I was pretty sure the problem had to do with exactly what you suggested--that the DataContext had issues when managing its identity cache.

     

    Regardless, I think that the current behavior, while not necessarily a bug, is definitely unexpected.

     

    Unfortunately, the entity classes I'm dealing with are preexisting business objects, so modifying them just for Linq To Sql compatibility isn't feasible.

     

    I did, however, manage to find a workaround by using the overloaded Attach() method that takes two entities--the original and the changed version--so that I can still take advantage of the default update functionality of Linq to Sql.

     

    Having to maintain two copies of the object manually is kludgy at best, but for now will have to suffice.

     

    Saturday, June 14, 2008 6:03 AM
  • We've just hit this problem - it's a bit irritating.

    The solution would be to implement your Equals method as before but only use your primary key as the result for GetHashCode.
    e.g.

            public override bool Equals(object obj) {
                Instance tmp = obj as Instance;
                if (tmp != null) {
                    bool eq = true;
                    eq = eq && this.Id == tmp.Id;
                    eq = eq && this.Name == tmp.Name;
                    eq = eq && this.CountryId == tmp.CountryId;
                    return eq;
                }
                return false;
            }
            
            public override int GetHashCode() {
                return Id.GetHashCode();
            }
    This seems to work for me and it also meets the constaints where:

    	if (a.Equals(b)) Assert(a.GetHashCode() == b.GetHashCode())

    Are there any reasons why this isn't a solution?
    Wednesday, October 21, 2009 1:10 AM