Cascade delete in Entity Framework

I had a really hard time getting a simple cascade delete to work with the Entity Framework and I thought I had to write it down here so that I can remember it in the future.

I was trying to delete an item from my database with some children. But I got the error

A relationship is being added or deleted from an AssociationSet ‘FK_ItemChildren_Item’. With cardinality constraints, a corresponding ‘ItemChildren’ must also be added or deleted.

I started to google on it and I found this thread in the MSDN forums. The first suggestion where to add cascade delete to the database and then update the model to get the changes. I did that but I still got the same error. The problem where this.

The SSDL part of the edmx file were updated correctly.

<Association Name="FK_ItemChildren_Item">
 <End Role="Item" Type="Model.Store.Item" Multiplicity="1">
  <OnDelete Action="Cascade" />
 </End>
 <End Role="ItemChildren" Type="Model.Store.ItemChildren"
  Multiplicity="*" />
 <ReferentialConstraint>
 ..
 </ReferentialConstraint>
</Association>

But the CSDL part where not updated:

<Association Name="FK_ItemChildren_Item">
 <End Type="Model.Item" Role="Item" Multiplicity="1">
  <OnDelete Action="Cascade"></OnDelete>
 </End>
 <End Type="Model.ItemChildren" Role="ItemChildren"
  Multiplicity="*">
 </End>
</Association>

The line in “<OnDelete Action=”Cascade”></OnDelete>” was not updated for some reason. When I added that in the model it worked. I guess the designer is not 100% even though the released EF.

UPDATE!

Alexander Muylaert sent me this script via the comments.

Thanks for your blog.  I had this issue on a huge model.  Over 180 tables and hundreds of references.  I didn’t feel like checking all this manually.

I open the edmx file as an xmldocument and executed the following code.  It fixes this issue in batch…  The code is not cleaned, but I have already lost a complete day and I can’t afford to waste more time.  I would like to post this, so someone could use this code and post a cleaned version.

private static string MakeKey(string aAssociation, string aRole) {
        return aAssociation + "_/_" + aRole;
    }

    public static void FixCascadeDeleteIssues(XmlDocument XDOC, XmlNode SSDLNode, XmlNode CSDLNode, ref bool aChanged) {
        HashSet lst = new HashSet();

        foreach (XmlNode SSDLChild in SSDLNode) {
            if (SSDLChild.Name == "Association") {
                foreach (XmlNode EndChild in SSDLChild) {
                    if (EndChild.Name == "End") {
                        foreach (XmlNode DeleteChild in EndChild) {
                            if (DeleteChild.Name == "OnDelete") {
                                var x = DeleteChild.Attributes.GetNamedItem("Action");
                                if (x.Value == "Cascade") {
                                    lst.Add(MakeKey(SSDLChild.Attributes.GetNamedItem("Name").Value, EndChild.Attributes.GetNamedItem("Role").Value));
                                }
                            }
                        }
                    }
                }
            }
        }

        Dictionary FMaps = new Dictionary();

        foreach (XmlNode CSDLChild in CSDLNode) {
            if (CSDLChild.Name == "EntityContainer") {
                foreach (XmlNode ContainerChild in CSDLChild) {
                    if (ContainerChild.Name == "AssociationSet") {
                        foreach (XmlNode EndChild in ContainerChild) {
                            if (EndChild.Name == "End") {
                                string Association = ContainerChild.Attributes.GetNamedItem("Name").Value;
                                string Role = EndChild.Attributes.GetNamedItem("Role").Value;
                                string EntitySet = EndChild.Attributes.GetNamedItem("EntitySet").Value;

                                FMaps.Add(MakeKey(Association, Role), EntitySet);
                            }
                        }
                    }
                }
            }
        }

        foreach (XmlNode CSDLChild in CSDLNode) {
            if (CSDLChild.Name == "Association") {
                string Association = CSDLChild.Attributes.GetNamedItem("Name").Value;

                if (Association == "FK_DEV_PARENT_DEV_ID") {
                    Console.WriteLine("");
                }

                foreach (XmlNode EndChild in CSDLChild) {
                    if (EndChild.Name == "End") {

                        string Multiplicity = EndChild.Attributes.GetNamedItem("Multiplicity").Value;
                        if (Multiplicity == "*") {
                            continue;
                        }

                        string role = EndChild.Attributes.GetNamedItem("Role").Value;
                        string v;
                        if (FMaps.TryGetValue(MakeKey(Association, role), out v)) role = v;
                        string key = MakeKey(CSDLChild.Attributes.GetNamedItem("Name").Value, role);

                        if (lst.Contains(key)) {
                            bool hasdel = false;
                            foreach (XmlNode DeleteChild in EndChild) {
                                if (DeleteChild.Name == "OnDelete") {
                                    var x = DeleteChild.Attributes.GetNamedItem("Action");
                                    if (x.Value == "Cascade") {
                                        hasdel = true;
                                    }
                                }
                            }
                            if (!hasdel) {
                                XmlNode newSub = XDOC.CreateNode(XmlNodeType.Element, "OnDelete", NamespaceDelete);
                                XmlAttribute xa = XDOC.CreateAttribute("Action", null);
                                xa.Value = "Cascade";
                                newSub.Attributes.Append(xa);
                                EndChild.AppendChild(newSub);
                                aChanged = true;
                            }
                        }
                    }
                }
            }
        }
    }