Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> 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

Published Wednesday, September 9, 2009 6:56 PM by Ken Cox [MVP]

Comments

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, September 21, 2009 4:18 AM by yash

Well i hope you are not the only one. Thanks for the Post.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Sunday, September 27, 2009 2:00 PM by Jos Verbeek

Thanks for writing about your mistake, I had made the same one and thanks to you it didn't take me to long!!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Sunday, September 27, 2009 2:07 PM by Ken Cox [MVP]

@Jos: It's good to hear that my goal of reducing aggravation for others is being met.

It helps offset the feeling that I'm looking like the village idiot around here. <grin>

Ken

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, September 29, 2009 11:05 PM by Greg Goodall

Ken, I had the same error and your comment about the Identity column not also being set as primary key solved the problem.  thanks a ton.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, September 30, 2009 12:30 AM by Ken Cox [MVP]

@Greg: Thanks for checking in. This makes me wonder why the error message doesn't offer the primary key issue as a possible fix.

Ken

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 15, 2009 7:30 AM by Kevin Haesendonckx

Thanks man, this helped me a lot, keep up the good work

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 22, 2009 1:25 AM by purs

Thank you :) the post really helped me to figure out the error quickly.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, October 31, 2009 11:01 AM by squorpeeon

At least you didn't called a "dumb**s".

Thanks for the quick fix.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, November 24, 2009 10:27 PM by Paul

I made the same mistake , and now I fixed it ,thanks for your kindness.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, November 24, 2009 10:30 PM by Paul

I made the same mistake , and now I fixed it ,thanks for your kindness.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, December 11, 2009 11:23 AM by richard.buff@tasb.org

Ken,

I'm glad I found your post.  Certainly saved me a few hours of troubleshooting.  

Hey, Microsoft - maybe you can try coming up with errors that point developers to the problem instead of these cryptic messages.

Try replacing "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."

with

Unable to update the EntitySet 'Purchases' because the table has no Primary Key

Thanks, Ken, your post helped me alot.  :-)

Rich

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, December 23, 2009 11:52 AM by Milko

I should have looked at the warnings while generating the Model - they did say that the table did not have a primary key assigned.

Thank you Ken!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, December 28, 2009 8:09 AM by purs

Thank you :) Spent almost 30 minutes on trying to find the mistake, before started to search for answer in the web.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, January 19, 2010 2:15 PM by Stewart

Ha!  It would have been nice to explain how to "regenerate the .edmx file".

See, you can never explain enough!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, January 20, 2010 11:39 AM by Micah

Definitely saved me a few hours of troubleshooting.

Thank you!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, March 15, 2010 1:45 PM by Matt

Thank you, that error msg is simply nuts.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, March 25, 2010 11:47 PM by Nate Reid

Ahh, thank you so much!  This is the sort of mistake you make when it's far to late at night.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, April 17, 2010 7:34 PM by Randir

Thanks!!! It saved me me time at 1am!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Sunday, May 16, 2010 12:07 PM by Justin Soliz

really appreciate it! saved me an afternoon...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, June 8, 2010 1:39 PM by John Cole

This is another WTH moment for Microsoft.  My table has a primary key, it just isn't an identity key.  Adding an identity key (which for this tiny configuration table is absolutely unnecessary) solved the problem.  Thanks for the good work.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, June 17, 2010 9:06 PM by Adam

Saved the day .. and hours of time .. thanks...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, June 23, 2010 6:31 AM by Mayuresh

Thanks buddy for documenting this. I ran into this error and was at a loss about how to fix it.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, July 8, 2010 9:32 AM by Umesh K

Needle in the Hay-Stack kind of situation. What made it worse for me was I read your article towards the End of the mission.

Anyways, Alls well that ends well.

THANKS LOADS.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, July 12, 2010 8:08 AM by Mr Dean

As per the other comments, thanks a bunch for posting the fix.

You have saved my skull from numerous bruises from banging my head against my desk.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, July 21, 2010 9:11 AM by Nitin

Thanks Ken

that really helped me a lot and saved my time...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, September 3, 2010 7:15 AM by leommoore

Thanks Ken, stupid mistake I know, but the error message is not exactly clear. Thanks for saving me from wasting too much time on this.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, September 6, 2010 4:36 PM by Drew

Saved me a bunch of time, thanks very much.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, September 8, 2010 10:04 AM by Kim

Thanks you saved me a lot of time.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, September 11, 2010 1:38 AM by Gary A. Stafford

Thanks! Saved me a lot of additional troubleshooting while doing the ASP.NET MVC tutorials on www.asp.net/.../getting-started-with-mvc-part4

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, September 29, 2010 5:33 AM by amorphius

Doesn't help...

I have two tables - Events(primary key is ID) and Visitors(primary key is VisitorId).

I've added table Events_has_Visitors with only 2 fields EventId and VisitorId(primary key is EventId) and make relation Events-Events_has_Visitors and Visitors-Events_has_Visitors, but when I update Entities, the relation between Events and Visitors becomes 0..1 to many, not many-to-many. Only deleting primary key from Events_nas_Visitors allow to make relation many-to-many

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, September 30, 2010 4:53 AM by Jan

Thanks man, your post saved me a lot of time.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, October 12, 2010 1:56 PM by Joe B.

THANKS.  I was following a tutorial and hit this error.  Your fix was fast enough to try and it worked like a charm.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 21, 2010 2:44 PM by Sten-Åke Strid

I just wanted to say that you helped me too. Thank you.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, October 23, 2010 3:11 PM by Abhay C

This was really helpful...just would like to add that "regenerate the .edmx file" which you mentioned-- for that I initially deleted edmx file and also recoded the controller. However, that is not necessary-- as you can double click the edmx file and then right click on the table name and click on 'Update model from database'.

in Visual Studio 2010.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 28, 2010 5:02 PM by mailfolorunso

Great !! God bless you bless you for this post.Being battling with this error for three days now ! Just fixed the 'dumb' error.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 28, 2010 10:00 PM by Kun

Your post saved me a lot of time. Thank you!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, November 19, 2010 9:10 AM by Swetha

Excellent !!! worked perfectly !!

Thank you so much....

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, November 27, 2010 8:18 AM by Martin

Thanks Ken,

saved me a lot of time

best regards...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, December 9, 2010 5:39 PM by Vipul

thanks for the post.  i wasted a couple of hours trying to figure this out until i read this.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, December 17, 2010 1:50 PM by pablop

lol! in general, i do write :p). (in general..lol).

thanks, very very useful!

saved me a lot of time !

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, January 12, 2011 7:23 AM by r.zarei

tanks man. it save me.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, January 13, 2011 8:29 AM by PLC

thank you so much, rescued me!!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, January 15, 2011 10:10 PM by Gabriel Brancolini

Thanks to you and please, thanks to your wife too!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, January 25, 2011 5:28 PM by Rudyard

I had the same problem and your post solved it, Thanks and keep blogging!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, January 26, 2011 8:12 AM by Nimi

Thanx! Happen to me just now...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, March 7, 2011 5:44 PM by Naren

Well i hope you are not the only one. Thanks for the Post.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, May 10, 2011 5:00 AM by John Braham

Thanks man, sometimes the obvious really does need stating! :)

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, June 3, 2011 11:10 AM by Charles

Great, great

Thanks a lot

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, June 6, 2011 7:42 AM by ricardo

saved my life, I was looking for it to 3 days

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Sunday, June 12, 2011 1:18 AM by Ray

Setting the primary key fixed it thanks!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, June 16, 2011 12:35 PM by Joe

Thanks for sharing. It really helped, just like the others commented.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, July 5, 2011 12:52 PM by gigo

thanks, that was exactly what i forgot.. setting the primary key.. :)

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, August 8, 2011 7:58 AM by Smitha

Thanks a lot lot lot for your post, Really so happy to save time because of you.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, August 26, 2011 5:30 AM by Joe

Thank you, thanks to you I saved a lot of time

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, September 10, 2011 6:12 AM by Karthik D V

Superb Buddy ! you took me away from the well !!!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, October 4, 2011 10:36 PM by ZMG

Yep, Very helpfull..... but some tables really wouldn't need the primary key..... (:

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, October 10, 2011 3:02 PM by Vova

Thank you very much!!! You are real programmer!!!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, October 20, 2011 11:02 AM by apoco76

Yep, I just made the same mistake! Thanks for posting it!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, November 5, 2011 11:49 AM by jonx

I was missing the key too. thank your for your post. thanks to you I solved my problem in no time...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Sunday, November 13, 2011 10:04 PM by Caio

Tks!!! you helped me a lot

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, November 17, 2011 1:11 AM by Sameer

Thanks mate it really helped

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, November 18, 2011 4:38 AM by Vijay

I did the same mistake...Your answer is amazing..It saved lot of time..

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, December 2, 2011 1:35 PM by Scott

There are million of these comments already, but again... thanks for the post, had the problem fixed within minutes...

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, December 30, 2011 9:17 PM by HasanRizvi

This is the case for me. Simply removing <DefiningQuery> resulted in another error.  I followed the steps of this post except the last one. For your convenience, I copied the 4 steps from the post   that I followed to solve the problem as following:

   Right click on the edmx file, select Open with, XML editor

   Locate the entity in the edmx:StorageModels element

   Remove the DefiningQuery entirely

   Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid)

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, January 27, 2012 12:11 AM by Dan

Old post, still relevant...  DOH! Forgot the Primary Key...  Guess that happens when you haven't created a DB table in 3 months...  Thanks, bud!  *cheers*

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, January 31, 2012 1:10 AM by Klaus Ahlbäumer

Thank you for writing your experience into this blog.

So it took me only a few minutes to fix the problem.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, February 24, 2012 6:29 AM by B

Thanks... same goes here.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, April 19, 2012 7:26 PM by UU

Many thanks for this note.

It saved me loadsa time

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, April 24, 2012 8:32 AM by Narendra

Thanks a lot.It saved a lot of time. Many thanks.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Tuesday, May 15, 2012 7:57 AM by Max

Thanks, saved me some time as well!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, May 17, 2012 11:05 AM by JD

Thanks for the Post - saved me some time

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, May 25, 2012 1:50 PM by Shivani

Thanks,Saved a lot of time.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, June 11, 2012 1:58 AM by Shweta

Thanks for writing this, I had made the same mistake and thanks to you it didn't take me to long!!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, June 15, 2012 7:28 AM by MrZ

Thanks, saved a lot of time!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, August 17, 2012 5:14 AM by Jay

I love you - you're blog saved me hours!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, August 22, 2012 10:41 AM by Nicole

I just wanted to add something else. If you are working with Visual Studio 2008 never forget to recreate the EDMX file. Otherwise you might be getting the same error.

This happened to me, but after I added the PK and re-create the EDMX file everything worked perfect.

Thanks Ken.

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Wednesday, September 5, 2012 7:25 AM by kafiluddin mohammed

Ken, i had the same issue, i implemented the concept you mentioned and it solved my problem....

Thanks,

Kafil

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, October 6, 2012 5:45 AM by Kal

"...you’ve discovered this little fix before losing time like I did."  Indeed! Quite! Thank you!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Friday, January 11, 2013 10:37 AM by Marcio

Thanks, same problem

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Monday, January 14, 2013 9:47 PM by Reggie

Than you so much, this saved my miserable ass!

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Saturday, January 19, 2013 12:21 PM by Armin - FIT

Thanks a lot, saved me also! :)

# re: Fixing the System.Data.UpdateException – DefiningQuery and no <InsertFunction> Error

Thursday, April 25, 2013 11:10 AM by Ricky

Thanks for posting this. I was about to undo my changes and start from scratch.

Leave a Comment

(required) 
(required) 
(optional)
(required)