13

I have DB model with table per type inheritance. For example, entities are A, B, C, A1, A2. Base - A Derived - A1, A2. Another - B, C. So, A has 1 to 1 association to A1 and A2. B and C has associations(1 to many, with OnDelete action on the DB side) to A1 and A2 respectively.

Problem

I trying to delete record from B, so I expect that EF remove also all A1 objects which associated to current B's record.

In the end, EF remove record from B and all associated records from A1, but not from A

Why? how fix it?

Developex
  • 328
  • 5
  • 11

2 Answers2

7

It's a known problem and I would call it a bug. Obviously only deleting the records from the table A1 for the derived entity's properties cannot be correct. The remaining data in the database (in table A) do represent another object type. In other words: This DELETE didn't actually delete an entity but it changed the entity's type = transformed an object of type A1 into an object of type A - which makes even less sense if A is an abstract entity.

The recommended workaround from here (as I understand it) is ugly:

var b = context.Bs.Include("A1s").Single(b => b.Id == 1);
foreach (var a1 in b.A1s.ToList())
    context.As.Remove(a1);
context.Bs.Remove(b);
context.SaveChanges();

context.As.Remove(a1); should delete from both A and A1 table, thereby fixing the problem of the orphaned records in table A. Unfortunately you are forced to load the children from the database to delete the parent correctly.

Here is another question and answer about this problem: Problems using TPT (Table Per Type) in EF 4.2 and deletion of parent objects

Community
  • 1
  • 1
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • Thanks! did mictosoft's team know about this bug ? :) do you know when it will be fixed? because this approach is very ugly. but it works :) – Developex Jan 31 '12 at 07:59
  • @user861108: No, I don't know if or when it will be fixed. I even don't know if MS considers the problem as a bug. – Slauma Jan 31 '12 at 11:46
  • I think that in those situations all will be OK if uses another type of inheritance. I used table per type inheritance. Maybe If I will be use Table Per Hierarchy this problem won't arise. What do you think ? – Developex Jan 31 '12 at 14:49
  • @user861108: Yes, I am pretty sure that the problem disappears with TPH inheritance. It is indeed TPT specific. But TPH has other flaws like mixing all properties into one table, nullable columns where properties of derived entities are actually required etc. I'd probably prefer to live with the workaround above. BTW: Can you test if the code above still works when you remove the `foreach` loop? I know that EF sends separate DELETE statements IF then children are loaded into the context (which happens here with `Include`). Perhaps these DELETES will also remove the rows from table `A`. – Slauma Jan 31 '12 at 16:36
  • I already did it(remove `foreach` loop). And EF thrown InvalidOperationException : The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted. – Developex Feb 01 '12 at 07:37
0

I had the same problem and a colleague told me to iterate over the collection of items before doing Remove(o) and suddenly it all worked.

naskew
  • 2,091
  • 1
  • 19
  • 20