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

Solution 1

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.

Solution 2

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

Solution 3

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)

Solution 4

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

Solution 5

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!

View more solutions

Comments

[Geoff]

Make sure to also change store:Schema to just Schema for that EntitySet, if you're still having troubles.

[Vivian River]

So, the solution is to add a primary key then?

[Suncat2000]

Then delete and recreate the entity because update doesn't work right in the EF designer.

[nrod]

PK was the answer. Thanks!

[StillLearnin]

Update in the EF designer worked fine for me after adding Primary Key to database. Using EF 5.0 and .net 4.0

[ajzeffer]

Same here ! Thx ... had to remove table and re add to EF to get it to take though

[eugenekgn]

What if you cannot add an primary key

[Michael Hornfeck]

Is this how generating EDMX has always worked? I'm used to working with Code First which doesn't require a PK on a pure join table.

[Obl Tobl]

Found this answer after finding the same solution to the problem. Definitely the correct answer! Only defining a primary key like mentioned in other answers won't help in many cases.

[puddinman13]

PK. Who would have guessed.

[J.Olsson]

Migrated the data to antoher DB. And forgot to maintain the key. This answer was the solution (created a script to reproduce the key)

[Talal Yousif]

I am using a view and have an instead of trigger on it. is there anyway I can use the view through EF to insert, update and delete? <br/> Edit: I made use of this answer of your. @LadislavMrnka

[Bashar Abu Shamaa]

and don't forget to click on "Update Model from Database" on your .edmx file

[Raj Chaurasia]

My table was missing Primary key. I updated the table and it started working

[Bicycle Dave]

Thank you very much - this is exactly what fixed my problem. Pretty disturbing that this hasn't been fixed in EF. And, pretty amazing that you figured this out!

[vintastic]

I tried deleting the entity and re-adding it. Recompiling. Cleaning. Nothing worked for me except this.

[Atta H.]

I was having the same issue. I didn't have any PK column in database, and when i added model, it set two columns as entity key. Later i set PK but forget to update the model. I just update the model and everything was fine

[qxotk]

Added PK in SSMS, opened model in VS, updated from database, ran custom tool on .tt files, rebuild solution, works now - that's the steps I followed for success.

[swcraft]

This solved my issue yet I don't know how you came up with answers and why your suggestion solved the issue.

[Dan]

PK was the problem here too, it had been added to the DB but wasn't in the model. Update didn't work, but removing and readding the entity did, as suggested above.

[Gary]

What happens if you need to update the database model? I did an "Update Model from Database" and it left my model totally unusable. I had to undo and start over again. If there a way around this?

[Dinesh Falwadiya]

In my case , there was no primary key. Thanks

[theTechRebel]

Add primary key and don't forget to update your Entity Framework model afterwards

[r3dst0rm]

That's a really weird issue. Is there any information on how this problem occurs in order to avoid it? Nonetheless - it helped

[keeehlan]

@BasharAbuShamaa this answer is not valid without that detail.

[SouravOrii]

A primary key was needed for the table. Also needed to update model from database on the .edmx file for my EF connection.

[Luke Duddridge]

Thanks for the save for the second time! I completely forgot this fix, if I could up vote again I would

[Kai Hartmann]

How to overcome, if we cannot change the database table?

[GregH]

Also worth noting the column must be set to be an identity column, not only a primary key

[Chris Schaller]

If you can change the DB table to have a primary key then the code generator will stop making the same mistakes, removing the key from EF will cause many other issues.

[AlejandroDG]

On mine case was Database table doesn't have a primary key