DefiningQuery and no <DeleteFunction> element exists in the <ModificationFunctionMapping> element to support the current operation

Question

Unable to update the EntitySet 'InstanceObjectName' because it has a DefiningQuery and no element exists in the element to support the current operation

Answer 1

Make sure that your table has a primarykey!

Answer 2

the Entity Framework doesn't know whether a given view is updatable or not, so it adds the element in order to safegaurd against having the framework attempt to generate queries against a non-updatable view.

If your view is updatable you can simply remove the element from the EntitySet definition for your view inside of the StorageModel section of your .edmx, and the normal update processing will work as with any other table.

If your view is not updatable, you will have to provide the update logic yourself through a "Modification Function Mapping". The Modification Function Mapping calls a function defined in the StorageModel section of your .edmx. That Function may contain the name and arguments to a stored procedure in your database, or you can us a "defining command" in order to write the insert, update, or delete statement directly in the function definition within the StorageModel section of your .edmx.

Of the two options, if your view is updatable (which it sounds like it may be) the easiest is certainly to remove the inserted by the designer.

Answer 3

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've 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 once your'e comfortable with this, 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. Basically, what we're going to do is lie to the Entity Framework and tell it there's a 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!