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

I am using Entity Framework 1 with .net 3.5.

I am doing something simple like this:

var roomDetails = context.Rooms.ToList();

foreach (var ro开发者_如何学JAVAom 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 <UpdateFunction> element exists in the <ModificationFunctionMapping> 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...

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.

Just Add a primary key to the table. That's it. Problem solved.

ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY(<COLUMN_NAME>)

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:

1. Right click on the edmx file, select Open with, XML editor
2. Locate the entity in the edmx:StorageModels element
3. Remove the DefiningQuery entirely
4. Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid)

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

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

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

2. Look for a line like this:
   <EntitySet Name="table_name" EntityType="MyModel.Store.table_name" store:Type="Tables" store:Schema="dbo" store:Name="table_nane">

3. Remove store:Name="table_name" from the end.

4. Change store:Schema="whatever" to Schema="whatever"

5. Look below that line and find the <DefiningQuery> tag. It will have a big ol' select statement in it. Remove the tag and it's contents.

6. Now your line should look something like this:
   <EntitySet Name="table_name" EntityType="MyModel.Store.table_name" store:Type="Tables" Schema="dbo" />

7. We have something else to change. Go through your file and find this:
   <EntityType Name="table_name">

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

9. Below is the <Key> 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:

<EntityType Name="table_name">
              <Key>
                <PropertyRef Name="order_numbers" />
                <PropertyRef Name="customer_name" />
              </Key>

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 => 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!

This can also happen if data model is out of date.

Hopefully this will save someone else frustration :)

The error may have occurred if your table doesn't have a primary key, in this case the table is "read only", and the db.SaveChanges () command will always error.

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.

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

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

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 !!!

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.

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

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.

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

I had the exact same problem, unfortunately, adding the primary key doesn't solve the issue. So here's how I solve mine:

1. Make sure you have a primary key on the table so I alter my table and add a primary key.
2. Delete the ADO.NET Entity Data Model (edmx file) where I use to map and connect with my database.
3. Add again a new file of ADO.NET Entity Data Model to connect with my database and for mapping my model properties.
4. Clean and rebuild the solution.

Problem solved.

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

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.

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.

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.

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.

<#@ template language="C#" debug="false" hostspecific="true"#>
<#@ output extension="/" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Windows.Forms" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq"#>
<#@ assembly name="%VS120COMNTOOLS%..\IDE\EntityFramework.dll" #>
<#@ assembly name="%VS120COMNTOOLS%..\IDE\Microsoft.Data.Entity.Design.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Windows.Forms" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.Globalization" #>
<#@ import namespace="System.Reflection" #>
<#@ import namespace="System.Data.Entity.Core.Metadata.Edm" #>
<#@ import namespace="System.Data.Entity.Core.Mapping" #>
<#@ import namespace="System.CodeDom" #>
<#@ import namespace="System.CodeDom.Compiler" #>
<#@ import namespace="Microsoft.CSharp"#>
<#@ import namespace="System.Text"#>
<#@ import namespace="System.Diagnostics" #>

<#
    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<XComment>().Where(c => 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 => 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);
#>

I fixed it by just deleting and rebuilding the EF project and all its classes. See details below:

I went round and round on this one for an hour, then deleted the EF project containing the error, saving out all my custom code, then rebuilding the whole thing. Since it was just a matter of generating a new EF diagram and its inner code, this process of fixing the problem took about 10 minutes.

The problem, I suspect, is that the database-first method for generating EF classes and properties gets a little weird after two or three "updates". Basically, I found a problem in the code behavior, went and fixed the database table properties, and then updated the EF diagram one time too many. Then I tried fiddling with the code, which didn't work.

Starting over worked.