0

I'm having a problem in which the deletion of a record in a table works, but only so long as the table has no child foreign key relationships. Now, when we have a parent table and want to delete the record using EF, it fails with an error of, "The relationship could not be changed because one or more of the foreign-key properties is non-nullable." I've deleted cascading delete on the child table referencing the parent table. I've tested it within SSMS. It works fine. But from within C# it fails. We have a view model where we've defined a generic DeleteRow method. This works great, so long as the row being deleted has no child tables. It fails, if the table is a parent table. I tried looking here on Stackoverflow for an answer and came across this question, now closed: stackoverflow.com/questions/17723276/delete-a-single-record-from-entity-framework. The answer to this question used a discrete table in EF. We're using generics. Here's our code:

public void DeleteRow(T row)
{
    if (App.MainDataContext != null && row != null)
    {
        App.MainDataContext.Entry<T>(row).State = EntityState.Deleted;
        App.MainDataContext.SaveChanges();
    }
}

I don't believe setting the State property to the enumeration EntityState.Deleted is working, as that's only setting it for the record in the parent table, not for any of the related records in the child tables. (MainDataContext is entity from a .EDMX file.) How do we delete a single record from any table using EF 6 when we're using generics to pass in the table type?

Community
  • 1
  • 1
Rod
  • 4,107
  • 12
  • 57
  • 81
  • How about [this](http://stackoverflow.com/questions/41858097/entity-framework-hard-cascade-delete/41859633#41859633)? Actually I see it's the same as the link you provided. All you need is to translate it, for instance: `App.MainDataContext.Set().Attach(row); App.MainDataContext.Set().Remove(row);` – Ivan Stoev Jan 31 '17 at 18:58

1 Answers1

0

The problem is that when you set the state to deleted, the related entity is also loaded (tracked by the context's change tracker). When you delete your row, EF tries to set the parent navigation property to null, but it is a not nullable relationship.

To solve this you have to set a cascading delete in the relationship in you model (EDMX), not only in the database.

It should work also, if by the instance of your context the related entity is not tracked. In this case on SQL-Server side the cascading delete should work.

dvjanm
  • 2,351
  • 1
  • 28
  • 42
  • Interesting and for me unexpected. The cascading delete is defined in the database. I got into the EF model (the .edmx file) in the designer and did a refresh of the entities there based upon the database. I had thought that would have been carried over into the EF model. What you're saying suggests to me that it isn't, which surprises me. – Rod Jan 31 '17 at 20:00
  • I concluded only from your error message, but you can check it in the properties of the association set, I think that it is not cascading, so the navigation property is set to null, which is a logical action by EF. – dvjanm Jan 31 '17 at 20:11
  • I just tried setting the "End1 OnDelete" property to Cascade (it was None, as you predicted) on the association. And also did the same thing with another child table that's in a similar relationship to the parent table. Then I compiled it. Now, I've got 156 errors instead of 0. Errors like VS can't find tables that are in the .edmx diagram. I'm guessing I've done the wrong thing, in an attempt to set cascading deletes in the .edmx diagram. Where should I do it? – Rod Jan 31 '17 at 20:18