none
Cannot insert explicit value for identity column in table [TABLENAME] when IDENTITY_INSERT is set to OFF. Explicit Value is NOT Desired.

    Question


  • I have a problem  with Entity Framework in VS2008 involve an INSERT.  It is illustrated in the following snippet.

    ObjectResults
    objectResult = results.WrappedObjectResults;
    _dataContext.AddToObjectResults(objectResult);
    _dataContext.SaveChanges();  // Throws an exception with title message

    The ObjectResults table has an integer identity primary key.  The WrappedObjectResults has the associated key field initialized to zero.  I am using the same scenario on a similar table without problem.  I do not have the option of setting the value to null since it is a value type.

    I do not want to set an explicit value, rather I wish to let SQL Server 2005 use the auto increment to set the primary key value.

    Any input would be welcome.

    Thanks,
    Eagle


    Wednesday, September 02, 2009 8:52 PM

Answers

  • How is results.WrappedObjectResults set to an object value?

    If the Id of the ObjectResults table in the SSDL is set to StoreGeneratedPattern=identity, then the EF will not attempt to insert the value and will just read back the value set by the database. If you set the id on the ObjectResults table after you reverse-engineered your model, what you can do is use the "Update Model from Database" feature: Running it will refresh your SSDL and ensure that the StoreGeneratedPattern is correctly set on the ObjectResults table.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by EagleRed Thursday, September 03, 2009 2:06 PM
    • Unmarked as answer by EagleRed Thursday, September 03, 2009 2:06 PM
    • Marked as answer by EagleRed Thursday, September 03, 2009 2:07 PM
    Wednesday, September 02, 2009 9:23 PM
    Moderator

All replies

  • How is results.WrappedObjectResults set to an object value?

    If the Id of the ObjectResults table in the SSDL is set to StoreGeneratedPattern=identity, then the EF will not attempt to insert the value and will just read back the value set by the database. If you set the id on the ObjectResults table after you reverse-engineered your model, what you can do is use the "Update Model from Database" feature: Running it will refresh your SSDL and ensure that the StoreGeneratedPattern is correctly set on the ObjectResults table.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by EagleRed Thursday, September 03, 2009 2:06 PM
    • Unmarked as answer by EagleRed Thursday, September 03, 2009 2:06 PM
    • Marked as answer by EagleRed Thursday, September 03, 2009 2:07 PM
    Wednesday, September 02, 2009 9:23 PM
    Moderator
  • I found the solution before seeing this response above.  When the ObjectResults table was defined its primary key, an int, was not marked as identity.  When the first EF object context was built and tested I noticed that the inserted record had a key value of zero, an unexpected result.  The database schema was modified and apparently the "Update Model from Database"  did not pick up this change.  I deleted the object context from the project and recreated it.  When testing I saw the expected behavior.

    Thanks for the insight,
    Eagle
    Thursday, September 03, 2009 2:15 PM
  • yea....dude...you r right..............
    Friday, April 15, 2011 3:28 PM
  • It worked for me too. Thanks.

    Thursday, February 23, 2012 5:42 PM