locked
Many to many cascade delete problem RRS feed

  • Question

  • Hi guys,

    I've got two entities articles and tags, which has many to many relation.

    After generation of database i get three tables:

    1. Article

    ID
    Title
    Text

    2. Tag

    ID
    Name

    3. TagArticle

    ArticleID
    TagID

    I want cascade delete from TagArticle table if Article or Tag is deleted.

    Currently I failed to find how to do it in entity framework and set cascade rules manually in sql managment studio.

    How can i configure the same in entity designer?

    Tuesday, February 16, 2010 9:44 AM

Answers

  • Hello Alexey,

     

    It seems that the Cascade Delete attribute is only valid on the “1” side of the relationship.  So for the many-to-many relationship, setting the Cascade Delete attribute on either side in CSDL is invalid (we will receive some compiler or validation error in the EDM). 

     

    The workaround would be setting the Cascade Delete attribute on the SSDL since many-to-many relationship is actually built up by two one-to-many relationship.  

     

    SSDL:

    =================================================================================

          <Association Name="FK_TagArticle_Article">

              <End Role="Article" Type="TestDBModel.Store.Article" Multiplicity="1">

                <OnDelete Action ="Cascade" />

             </End>

              <End Role="TagArticle" Type="TestDBModel.Store.TagArticle" Multiplicity="*" />

              <ReferentialConstraint>

                <Principal Role="Article">

                  <PropertyRef Name="ArticleID" />

                </Principal>

                <Dependent Role="TagArticle">

                  <PropertyRef Name="ArticleID" />

                </Dependent>

              </ReferentialConstraint>

            </Association>

            <Association Name="FK_TagArticle_Tag">

              <End Role="Tag" Type="TestDBModel.Store.Tag" Multiplicity="1">

                <OnDelete Action="Cascade" />

             </End>

              <End Role="TagArticle" Type="TestDBModel.Store.TagArticle" Multiplicity="*" />

              <ReferentialConstraint>

                <Principal Role="Tag">

                  <PropertyRef Name="TagID" />

                </Principal>

                <Dependent Role="TagArticle">

                  <PropertyRef Name="TagID" />

                </Dependent>

              </ReferentialConstraint>

            </Association>

    =================================================================================

     

    Testing codes:

    =================================================================================

                    var article = context.Article.Include("Tag").First();

                    context.DeleteObject(article);

                    context.SaveChanges();

    =================================================================================

    We need to retrieve the related entities to let the cascade delete work in EF side, so we call .Include() method here.  

     

    Based on this blog article, http://blogs.msdn.com/alexj/archive/2009/08/19/tip-33-how-cascade-delete-really-works-in-ef.aspx, we also need to pay attention to some important points:

     

    "So here are the golden rules:

    1. If you add an Cascade delete rule to the model, you MUST have a corresponding DELETE rule in the database.
    2. If you absolutely insist on breaking rule (1) for some reason, Cascade will only work if you have all the dependents loaded in memory.
    3. (2) is *not* recommended!!!

     

    So please keep your cascade rules at the database side as well.  

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 17, 2010 5:06 AM

All replies

  • Hello Alexey,

     

    It seems that the Cascade Delete attribute is only valid on the “1” side of the relationship.  So for the many-to-many relationship, setting the Cascade Delete attribute on either side in CSDL is invalid (we will receive some compiler or validation error in the EDM). 

     

    The workaround would be setting the Cascade Delete attribute on the SSDL since many-to-many relationship is actually built up by two one-to-many relationship.  

     

    SSDL:

    =================================================================================

          <Association Name="FK_TagArticle_Article">

              <End Role="Article" Type="TestDBModel.Store.Article" Multiplicity="1">

                <OnDelete Action ="Cascade" />

             </End>

              <End Role="TagArticle" Type="TestDBModel.Store.TagArticle" Multiplicity="*" />

              <ReferentialConstraint>

                <Principal Role="Article">

                  <PropertyRef Name="ArticleID" />

                </Principal>

                <Dependent Role="TagArticle">

                  <PropertyRef Name="ArticleID" />

                </Dependent>

              </ReferentialConstraint>

            </Association>

            <Association Name="FK_TagArticle_Tag">

              <End Role="Tag" Type="TestDBModel.Store.Tag" Multiplicity="1">

                <OnDelete Action="Cascade" />

             </End>

              <End Role="TagArticle" Type="TestDBModel.Store.TagArticle" Multiplicity="*" />

              <ReferentialConstraint>

                <Principal Role="Tag">

                  <PropertyRef Name="TagID" />

                </Principal>

                <Dependent Role="TagArticle">

                  <PropertyRef Name="TagID" />

                </Dependent>

              </ReferentialConstraint>

            </Association>

    =================================================================================

     

    Testing codes:

    =================================================================================

                    var article = context.Article.Include("Tag").First();

                    context.DeleteObject(article);

                    context.SaveChanges();

    =================================================================================

    We need to retrieve the related entities to let the cascade delete work in EF side, so we call .Include() method here.  

     

    Based on this blog article, http://blogs.msdn.com/alexj/archive/2009/08/19/tip-33-how-cascade-delete-really-works-in-ef.aspx, we also need to pay attention to some important points:

     

    "So here are the golden rules:

    1. If you add an Cascade delete rule to the model, you MUST have a corresponding DELETE rule in the database.
    2. If you absolutely insist on breaking rule (1) for some reason, Cascade will only work if you have all the dependents loaded in memory.
    3. (2) is *not* recommended!!!

     

    So please keep your cascade rules at the database side as well.  

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 17, 2010 5:06 AM
  • Hi Alexey,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, February 19, 2010 1:45 AM
  • Hello Lingzshi,

    How can I preserve SSDL changes? each time I open Entity framework model designer it overrides and remove my casacade delete attribute added to SSDL manually. I am using Entity framework 4.0 and model first approach.

    Please help.

     

    Thanks,

     

    -Murad

    Tuesday, September 28, 2010 3:57 PM