Cascade delete in Entity Framework

I hade 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 a item from my database with some childrens. 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 where updated correctly.

But the CSDL part where not updated:

The line in “<OnDelete Action=”Cascade”></OnDelete>” where 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.

10 Thoughts on “Cascade delete in Entity Framework

  1. Thanks for blogging this! You just saved me from a huge amount of frustration.

  2. What an complete pain. I suppose things like this will happen since it’s relatively new but not really an excuse. Thanks for the post though. Go go entity framework.

  3. Yeah, and the EF isn’t even very new. This is something that should be fixed.

  4. Dario on July 18, 2009 at 5:07 pm said:

    hi, thanks for the hint! it saved me a lot of frustration too!
    what a pity that this code generating part is not that reliable… but anyway, the problem is solved!

  5. Hi,

    I have some problem with cascade delete in EF and Im wondering if U could help me.

    In my application I have Customer with collection of Invoices. Each Invoice has collection of Products. Im loading Customer objects using linq to entity query:
    var selectCustomer = from c in dbContext.Customer.Include(“Invoice.Product”)
    where c.Id == id
    select c;
    And everythig is ok, I have Customers with Invoices and its Products. But when Im trying to delete single Customer object, EF deletes from DB only customer and invoices, but it isnt deleting products that refer to invoice in junction table.

    Can anyone help?

  6. Hi

    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;
    }
    }
    }
    }
    }
    }
    }

  7. Thanks for the tip Alexander!

    I added this to the main post :)

  8. Andrey on May 18, 2010 at 9:05 am said:

    Working version based on Alexander code :)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.IO;
    using System.Text;
    using System.Xml;

    namespace EntityRepareCascadeDelete
    {
    class Program
    {
    static void Main(string[] args)
    {
    string[] files = Directory.GetFiles(Directory.GetCurrentDirectory(), “*.edmx”);
    XmlDocument doc = new XmlDocument();

    bool aChanged = false;
    foreach (string file in files)
    {
    Console.WriteLine(“Load: ” + file);
    doc.Load(file);

    System.Xml.XmlNamespaceManager xmlnsManager = new System.Xml.XmlNamespaceManager(doc.NameTable);
    xmlnsManager.AddNamespace(“edmx”, “http://schemas.microsoft.com/ado/2007/06/edmx”);

    XmlNode SSDLNode = doc.SelectSingleNode(“//edmx:StorageModels”, xmlnsManager).FirstChild;
    XmlNode CSDLNode = doc.SelectSingleNode(“//edmx:ConceptualModels”, xmlnsManager).FirstChild;

    aChanged = false;
    FixCascadeDeleteIssues(doc, SSDLNode, CSDLNode, ref aChanged);

    if (aChanged)
    {
    Console.WriteLine(“Save: ” + file);
    string backupFile = file + “.bak”;
    if (File.Exists(backupFile))
    File.Delete(backupFile);

    File.Move(file, file + “.bak”);
    doc.Save(file);
    }
    else
    {
    Console.WriteLine(“Entity is correct: ” + Path.GetFileName(file));
    }
    }

    Console.WriteLine(“Press any key…”);
    Console.ReadKey();
    }

    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)
    {
    Console.WriteLine(string.Format(“Association ‘{0}’ not have cascade”, Association));
    XmlNode newSub = XDOC.CreateNode(XmlNodeType.Element, “OnDelete”, EndChild.NamespaceURI);
    XmlAttribute xa = XDOC.CreateAttribute(“Action”, null);
    xa.Value = “Cascade”;
    newSub.Attributes.Append(xa);
    EndChild.AppendChild(newSub);
    aChanged = true;
    }
    }
    }
    }
    }
    }
    }

    }
    }

  9. Adam on July 23, 2010 at 2:07 pm said:

    The script came in really handy for me too – I had been putting off manually checking my model for this issue for ages. However, I have a small amendment which covers a couple of cases which the script misses, which seems to occur due to renaming of association sets.

    Replace lines 62 to 66 in the script with the following to pick up a few more missing cascades:

    string key1 = MakeKey(CSDLChild.Attributes.GetNamedItem(“Name”).Value, role);
    string key2 = null;

    // Relationship may have been created before or after Set was renamed:
    if (FMaps.TryGetValue(MakeKey(Association, role), out role))
    {
    key2 = MakeKey(CSDLChild.Attributes.GetNamedItem(“Name”).Value, role);
    }

    if (lst.Contains(key1) || (key2 != null && lst.Contains(key2))) {

  10. Thanks for this tip, I can’t believe this important bug hasn’t been fixed yet :(

Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Post Navigation