8

I have a Model-First entity model which contains a Customer table linked to a view that fetches customer details from a separate database. The relationship is One to Many between the Customer table and the View and I have a navigation property on both the Customer entity and the View entity.

When I try to perform a delete using context.Customers.DeleteObject(cust) and call context.SaveChanges() I get an error:

Unable to update the EntitySet 'ViewEntity' because it has a DefiningQuery and no [DeleteFunction] element exists element to support the current operation.

I have tried setting On Delete Cascade and None and both generate the same error.

EDIT: There's not much code to show, but here you go:

Customer selectedCust = (Customer)dgvCustomers.SelectedRows[0].DataBoundItem;
if (selectedCust != null)
{
    if (MessageBox.Show(String.Format("Are you sure you want to delete Customer {0}?", selectedCust.CustomerID.ToString()), 
                            "Customer Delete Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
    {
        // TODO - Fix this
        this.ReportSchedDBContext.Customers.DeleteObject(selectedCust);
        this.ReportSchedDBContext.SaveChanges();                      
    }
}
Overhed
  • 1,289
  • 1
  • 13
  • 41
  • 1
    show us your code bro – jegtugado Apr 22 '16 at 00:59
  • 1
    Does your Entity and table have a primary key? – Overmachine May 03 '16 at 17:51
  • The entity based on a real table (Customer) does, the view-based entity does not since it's based on a View. – Overhed May 03 '16 at 17:54
  • Have you tried Remove instead of DeleteObject? Other than that hard to tell what's going on without seeing code of the context portions involved. – hubson bropa May 03 '16 at 21:33
  • Can you show the definitions (or diagram at least) of both your Customer table and that view? – Evk May 04 '16 at 06:37
  • 1
    Is it a self-tracking entity? You normally should follow such a pattern as retrieve the entity from db, mark as delete and save. – ali May 04 '16 at 08:35
  • I assume this is database first? It would help to see the essential part of the edmx diagram. You say the view entity doesn't have a primary key. It does. EF must have inferred a primary key, but not necessarily the correct one. This *may* cause trouble. – Gert Arnold May 07 '16 at 21:06

3 Answers3

1

Many related posts in SO agree with @Overmachine... you are probably missing a primary key on your entity/table.

See..

because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element

and

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

EDIT

Just saw your comment regarding your Customer table having a primary key but your view not. Not sure whats going on without more of your code, but ultimately all you need is a Customer object with the primary key(s) set on it, then you can use the following code to delete a detached entity object.

this.ReportSchedDBContext.Entry(selectedCust).State = EntityState.Deleted;
this.ReportSchedDBContext.SaveChanges();

If you are casting from another type and that is causing problems, you could also do:

var cust = new Customer { CustomerID = selectedCust.CustomerID };
this.ReportSchedDBContext.Entry(cust).State = EntityState.Deleted;
this.ReportSchedDBContext.SaveChanges();
Community
  • 1
  • 1
Chris Curtis
  • 1,478
  • 1
  • 10
  • 21
  • Hi Chris, Thanks for your help. I forgot to clarify that I'm on an older version of EF (v4), so most of what you posted doesn't apply to me (eg DBContext). I tried updating my version but a lot of things broke and I don't have time to tackle this now. – Overhed May 09 '16 at 00:02
1

I was able to work around this issue by creating a dummy Stored Procedure that does nothing ("SELECT 'Done'") and using the SP Function Mapping in my two Views to set the Delete function to this Stored Procedure. Quite a hack but it worked.

I'm not sure why a Delete Function is required for a View or if I'm doing something else wrong which caused the issue, but the above worked for me.

Overhed
  • 1,289
  • 1
  • 13
  • 41
0

I think you should use of all foreign keys in View. when you use all foreign keys and primary keys in view you can update and delete object as cascade.

M.Nasrifard
  • 34
  • 2
  • Thanks for your reply, but I didn't *want* do delete anything in the View. In fact I disabled Delete Cascade and I was still getting the same error. I had to basically define a dummy Delete Function with a Stored Procedure that does nothing and link the delete to the view for the logic to work. – Overhed May 10 '16 at 14:49