Monday, February 7, 2011

Fixing the System.Data.UpdateException – DefiningQuery and no Error

Here’s another in my never-ending series of fixes for dumb mistakes. In this episode we lose time with an Entity Framework error. Here’s the error message:
System.Data.UpdateException was unhandled by user code
  Message="Unable to update the EntitySet 'Purchases' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation."
  Source="System.Data.Entity"
This one had me baffled because I was mainly just cloning ASP.NET code that worked fine against a very similar database table. The code looked like this, with the error on the last line in boldface:
  Dim ent As New DBEntities1
  Dim pchase As New DBModel.Purchases
  pchase.OrderNumber = CleanString(txtPurchaseNumber.Text)
' ……….
  pchase.DateCreated = Now
  pchase.LastMod = Now
  ent.AddToPurchases(pchase)
  ent.SaveChanges()
I finally tracked it down to the SQL Server database table that I had recently added. I hadn’t marked the ID (identity) field as the primary key. (I thought I had but it wasn’t there when I went to look!) After that, just regenerate the .edmx file and the error is gone.
When I confide to my wife that I’ve made a stupid programming mistake, she insists that “surely others must do the same”. That might be the case, but most developers aren’t crazy enough to write a blog post to expose their folly in perpetuity to potential clients. <grin>
That said, I take comfort in the hope that you’ve discovered this little fix before losing time like I did.
Ken