Unable to update the EntitySet - because it has a DefiningQuery and no UpdateFunction element exist

Question

I am using Entity Framework 1 with  net 3 5   I am doing something simple like this   var roomDetails   context Rooms ToList     foreach  var room in roomDetails               room LastUpdated   DateTime Now      I am getting this error when I try to do    context SaveChanges      I get the error      Unable to update the EntitySet - because it has a DefiningQuery and no  lt UpdateFunction gt  element exists in the  lt ModificationFunctionMapping gt  element to support the current operation    I am doing lots of updates on the context and not having any issues  it s only when I try to update this particular entity   All my searching shows up the same thing  that there is no primary key declared on the entity that I m trying to update  But alas  I do have a Primary key declared

User · Answer

I had the exact same problem  unfortunately  adding the primary key doesn t solve the issue  So here s how I solve mine    Make sure you have a primary key on the table so I alter my table and add a primary key  Delete the ADO NET Entity Data Model  edmx file  where I use to map and connect with my database  Add again a new file of ADO NET Entity Data Model to connect with my database and for mapping my model properties  Clean and rebuild the solution     Problem solved

User · Answer

In my case  forgot to define Primary Key to Table  So assign like shown in Picture and Refresh your table from  Update model from Database  from  edmx file  Hope it will help

User · Answer

I was getting this issue because i was generating my EDMX from an existing database  designed by somebody else  and i use the term  designed  loosely here    Turns out the table had no keys whatsoever  EF was generating the model with many multiple keys  I had to go add a primary key to the db table in SQL and then updated my model in VS    That fixed it for me

User · Answer

UPDATE  I ve gotten a few upvotes on this lately  so I figured I d let people know the advice I give below isn t the best  Since I originally started mucking about with doing Entity Framework on old keyless databases  I ve come to realize that the best thing you can do BY FAR is do it by reverse code-first  There are a few good articles out there on how to do this  Just follow them  and then when you want to add a key to it  use data annotations to  fake  the key    For instance  let s say I know my table Orders  while it doesn t have a primary key  is assured to only ever have one order number per customer  Since those are the first two columns on the table  I d set up the code first classes to look like this        Key  Column Order   0       public Int32  OrderNumber   get  set          Key  Column Order   1       public String Customer   get  set      By doing this  you re basically faked EF into believing that there s a clustered key composed of OrderNumber and Customer  This will allow you to do inserts  updates  etc on your keyless table   If you re not too familiar with doing reverse Code First  go and find a good tutorial on Entity Framework Code First  Then go find one on Reverse Code First  which is doing Code First with an existing database   Then just come back here and look at my key advice again      Original Answer   First  as others have said  the best option is to add a primary key to the table  Full stop  If you can do this  read no further   But if you can t  or just hate yourself  there s a way to do it without the primary key   In my case  I was working with a legacy system  originally flat files on a AS400 ported to Access and then ported to T-SQL   So I had to find a way  This is my solution  The following worked for me using Entity Framework 6 0  the latest on NuGet as of this writing     Right-click on your  edmx file in the Solution Explorer  Choose  Open With     and then select  XML  Text  Editor   We re going to be hand-editing the auto-generated code here  Look for a line like this    lt EntitySet Name  table name  EntityType  MyModel Store table name  store Type  Tables  store Schema  dbo  store Name  table nane  gt  Remove store Name  table name  from the end  Change store Schema  whatever  to Schema  whatever  Look below that line and find the  lt DefiningQuery gt  tag  It will have a big ol  select statement in it  Remove the tag and it s contents  Now your line should look something like this    lt EntitySet Name  table name  EntityType  MyModel Store table name  store Type  Tables  Schema  dbo    gt  We have something else to change  Go through your file and find this   lt EntityType Name  table name  gt  Nearby you ll probably see some commented text warning you that it didn t have a primary key identified  so the key has been inferred and the definition is a read-only table view  You can leave it or delete it  I deleted it  Below is the  lt Key gt  tag  This is what Entity Framework is going to use to do insert update deletes  SO MAKE SURE YOU DO THIS RIGHT  The property  or properties  in that tag need to indicate a uniquely identifiable row  For instance  let s say I know my table orders  while it doesn t have a primary key  is assured to only ever have one order number per customer     So mine looks like    lt EntityType Name  table name  gt                 lt Key gt                   lt PropertyRef Name  order numbers    gt                   lt PropertyRef Name  customer name    gt                 lt  Key gt    Seriously  don t do this wrong  Let s say that even though there should never be duplicates  somehow two rows get into my system with the same order number and customer name  Whooops  That s what I get for not using a key  So I use Entity Framework to delete one  Because I know the duplicate is the only order put in today  I do this   var duplicateOrder   myModel orders First x   gt  x order date    DateTime Today   myModel orders Remove duplicateOrder     Guess what  I just deleted both the duplicate AND the original  That s because I told Entity Framework that order number cutomer name was my primary key   So when I told it to remove duplicateOrder  what it did in the background was something like   DELETE FROM orders WHERE order number    duplicateOrder s order number  AND customer name    duplicateOrder s customer name    And with that warning    you should now be good to go

User · Answer

so its true  just add a primary key  Note  be sure that when you re updating your EF diagram from the database that you re pointing to the right database   in my case the connection string was pointing to a local DB instead of the up-to-date Dev DB  schoolboy error i know  but I wanted to post this because it can be very frustrating if you re convinced you ve added the primary key and you re still getting the same error

User · Answer

Open Your  edmx file in XML editor and then remove  tag from  Tag and also change store Schema  dbo  to Schema  dbo  and  rebuild the solution now error will resolve and you will be able to save the data

User · Answer

I was getting the same error message  but in my scenario I was trying to update entities derived from a many-to-many relationship using a PJT  Pure Join Table    From reading the other posts  I thought I could fix it by adding an additional PK field to the join table    However  if you add a PK column to a join table  it is no longer a PJT and you lose all of the entity framework advantages like the automatic relationship mapping between the entities   So the solution in my case was to alter the join table on the DB to make a PK that includes BOTH of the foreign ID columns

User · Answer

just add a primary key to your table and then recreate your EF

User · Answer

I just had to remove the table from the model and update the model again bringing the table back  I guess the primary key was created after the table was pulled into the model

User · Answer

Just note that maybe your Entity have primary key but your table in database doesn t have primary key

User · Answer

Adding the primary key worked for me too    Once that is done  here s how to update the data model without deleting it -  Right click on the edmx Entity designer page and  Update Model from Database

User · Answer

This can also happen if data model is out of date   Hopefully this will save someone else frustration

User · Answer

I had the same issue  As this thread said  My table didn t have a PK  so I set the PK and ran the code  But unfortunately error came again  What I did next was  deleted the DB connection  delete  edmx file in Model folder of Solution Explorer  and recreated it  Error gone after that  Thanks everyone for sharing your experiences  It save lots of time

User · Answer

This is not a new answer but will help somebody who s not sure how to set primary key for their table  Use this in a new query and run  This will set UniqueID column as primary key   USE  YourDatabaseName  GO  Alter table   dbo   YourTableNname  Add Constraint PK YourTableName UniqueID Primary Key Clustered  UniqueID   GO

User · Answer

I found the original answer of updating the  edmx file work best in my situation  I just wasn t too happy about altering the model every time it was updated from the database  That s why I wrote an additional Text Template file  that is automaticaly invoked when after the model has changed - just like the entities are newly generated  I post it here in this comment  To make it work  make sure you name it like  model name  something tt  and store it in the same folder as your  edmx folder  I named it  model name  NonPkTables tt  It does not generate a file on its own due to the invalid file extension definition in the second line  Feel free to use    lt    template language  C   debug  false  hostspecific  true   gt   lt    output extension       gt   lt    assembly name  System Core    gt   lt    assembly name  System Data    gt   lt    assembly name  System Windows Forms    gt   lt    assembly name  System Xml    gt   lt    assembly name  System Xml Linq   gt   lt    assembly name   VS120COMNTOOLS    IDE EntityFramework dll    gt   lt    assembly name   VS120COMNTOOLS    IDE Microsoft Data Entity Design dll    gt   lt    import namespace  System    gt   lt    import namespace  System Windows Forms    gt   lt    import namespace  System Linq    gt   lt    import namespace  System IO    gt   lt    import namespace  System Collections Generic    gt   lt    import namespace  System Xml    gt   lt    import namespace  System Xml Linq    gt   lt    import namespace  System Globalization    gt   lt    import namespace  System Reflection    gt   lt    import namespace  System Data Entity Core Metadata Edm    gt   lt    import namespace  System Data Entity Core Mapping    gt   lt    import namespace  System CodeDom    gt   lt    import namespace  System CodeDom Compiler    gt   lt    import namespace  Microsoft CSharp   gt   lt    import namespace  System Text   gt   lt    import namespace  System Diagnostics    gt    lt       string modelFileName  this Host TemplateFile Split      0      edmx       string edmxPath   this Host ResolvePath  modelFileName            MessageBox Show  this Host TemplateFile     applied          var modelDoc   XDocument Load edmxPath       var root   modelDoc Root      XNamespace nsEdmx     http   schemas microsoft com ado 2009 11 edmx       XNamespace ns     http   schemas microsoft com ado 2009 11 edm ssdl        var runtime   root Elements nsEdmx    Runtime   First        var storageModels   runtime Elements nsEdmx    StorageModels   First        XNamespace nsStore     http   schemas microsoft com ado 2007 12 edm EntityStoreSchemaGenerator        var schema   storageModels Elements ns    Schema   First        XNamespace nsCustomAnnotation     http   schemas microsoft com ado 2013 11 edm customannotation        var entityTypes   schema Nodes   OfType lt XComment gt    Where c   gt  c Value Contains  warning 6002  The table view         bool changed   false       foreach  var node in entityTypes                var element   node ElementsAfterSelf   First            string entityName   element Attribute  Name   Value              Find EntitySet in EntityContainer          var entityContainer   schema Elements ns    EntityContainer   First            var entitySet   entityContainer Elements ns    EntitySet   First s   gt  s Attribute  Name   Value    entityName               Change  store Schema  attribute to  Schema  attribute          var attribute   entitySet Attribute nsStore    Schema             if  attribute    null                        string schemaName   entitySet Attribute nsStore    Schema   Value              entitySet Attribute nsStore    Schema   Remove                entitySet Add new XAttribute  Schema   schemaName                changed    true                        Remove the DefiningQuery element          var definingQuery   entitySet Element ns    DefiningQuery             if  definingQuery    null                        definingQuery Remove                changed    true                      Debug WriteLine string Format  Removed defining query of EntitySet  0     entityName                         if  changed          modelDoc Save edmxPath     gt

User · Answer

Set Primary Key then save Table and Refresh then go to Model edmx delete Table and get again

User · Answer

I had this issue come up and believe it was caused because I had deleted the Index on my tables primary key and replaced it with an index on some of the other fields in the table   After I deleted the primary key index and refreshed the edmx  inserts stopped working     I refreshed the table to the older version  refreshed the edmx and everything works again     I should note that when I opened the EDMX to troubleshoot this issue  checking to see if there was a primary key defined  there was   So none of the above suggestions were helping me   But refreshing the index on the primary key seemed to work

User · Answer

It usually happens because one of the following reasons    Entity Set is mapped from Database view A custom Database query Database table doesn t have a primary key   After doing so  you may still need to update in the Entity Framework designer  or alternatively delete the entity and then add it  before you stop getting the error

User · Answer

This is the case for me  Simply removing  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

User · Answer

Just Add a primary key to the table  That s it  Problem solved   ALTER TABLE  lt TABLE NAME gt  ADD CONSTRAINT  lt CONSTRAINT NAME gt  PRIMARY KEY  lt COLUMN NAME gt

User · Answer

The error may have occurred if your table doesn t have a primary key  in this case the table is  quot read only quot   and the db SaveChanges    command  will always error

[c#] Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist

Examples related to c#

Examples related to asp.net

Examples related to .net

Examples related to entity-framework

Examples related to primary-key