Entity Framework cascading delete problems - Foreign key set to null

Question

Hello, I have the following Model which I mapped with the Entity Framework: Mitglied -> Auftrag -> Teilprojekt

I have set up everything in the database with foreign keys and "on delete cascade". If I perform some tests on the database everything works fine. The problem arises as soon as I use the Entity Framework to add and especially delete objects. Consider the following code:

Mitglieder m1 = new Mitglieder();
m1.Name = "erstes";

Auftraege a1 = new Auftraege();
a1.Name = "a1";
m1.Auftraege.Add(a1);

Teilprojekte t1 = new Teilprojekte();
t1.Name = "t1";
a1.Teilprojekte.Add(t1);

context.AddToMitglieder(m1);


Mitglieder m2 = new Mitglieder();
m2.Name = "zweites";

Auftraege a2 = new Auftraege();
a2.Name = "a2";
m2.Auftraege.Add(a2);

Teilprojekte t2 = new Teilprojekte();
t2.Name = "t2";
a2.Teilprojekte.Add(t2);

context.AddToMitglieder(m2);
context.SaveChanges();

This adds correctly all the objects and sets the foreign keys. If I then execute the following code to delete the Auftraege:

 var members = context.Mitglieder.ToList();

 var mem1 = members.Single(m => m.Name == "erstes");
 mem1.Auftraege.Load();
 var auf1 = mem1.Auftraege.First();
 context.DeleteObject(auf1);

 var mem2 = members.Single(m => m.Name == "zweites");
 mem2.Auftraege.Load();
 var auf2 = mem2.Auftraege.First();

 //THIS IS THE LINE THAT MAKES THE DIFFERENCE
 auf2.Teilprojekte.Load();

 context.DeleteObject(auf2);

 context.SaveChanges();

The two Auftraege a1 and a2 are correctly deleted; also the Teilprojekt t1 is deleted but the Teilprojekt t2 is not deleted;

Its AuftragID column is set to NULL!

The only difference is that for a1 I did not load the Teilprojekte and for a2 I did load them. I don't understand why this is not working properly. Also in the SSDL everything is set up correctly:

    <Association Name="FK_Auftraege_Mitglieder">
      <End Role="Mitglieder" Type="TechnBuero.Store.Mitglieder" Multiplicity="0..1">
        <OnDelete Action="Cascade" />
      </End>
      <End Role="Auftraege" Type="TechnBuero.Store.Auftraege" Multiplicity="*" />
      <ReferentialConstraint>
        <Principal Role="Mitglieder">
          <PropertyRef Name="ID" />
        </Principal>
        <Dependent Role="Auftraege">
          <PropertyRef Name="Mitglieder_ID" />
        </Dependent>
      </ReferentialConstraint>
    </Association>
    </Association>
        <Association Name="FK_Teilprojekte_Auftraege">
      <End Role="Auftraege" Type="TechnBuero.Store.Auftraege" Multiplicity="0..1">
        <OnDelete Action="Cascade" />
      </End>
      <End Role="Teilprojekte" Type="TechnBuero.Store.Teilprojekte" Multiplicity="*" />
      <ReferentialConstraint>
        <Principal Role="Auftraege">
          <PropertyRef Name="ID" />
        </Principal>
        <Dependent Role="Teilprojekte">
          <PropertyRef Name="AuftragsID" />
        </Dependent>
      </ReferentialConstraint>
    </Association>

If I set the AuftragsID on the table Teilprojekte to not null then I get the following error from the Entity Framework:

Error 1 Error 3023: Problem in Mapping Fragments starting at lines 638, 702: Column Teilprojekte.AuftragsID has no default value and is not nullable. A column value is required to store entity data. 
An Entity with Key (PK) will not round-trip when:
((PK is in 'Teilprojekte' EntitySet OR PK plays Role 'Teilprojekte' in AssociationSet 'FK_Teilprojekte_Auftraege') AND (PK is NOT in 'Teilprojekte' EntitySet OR PK does NOT play Role 'Teilprojekte' in AssociationSet 'FK_Teilprojekte_Auftraege'))

Can anyone help me and tell me what is going wrong and how I can solve this issue? Thanks

Answer 1

The cascade delete in the EDMX will only cascade loaded (attached) entities. so unless your entity is loaded it is not going to cascade the delete. EF is expecting your db to cascade the delete.

1. Make sure you have a cascade delete set up in your database
2. Set the cascade delete in your EDMX
3. If you can't do 1, make sure you load all entities before deleting