locked
How to Switch On the Cascade Delete on a Many-to-Many Association in EF Code First CTP5? RRS feed

  • Question

  • In CTP4 the Cascade Delete on a Many-To-Many association (one that Code First creates a link table in between), was switched off by default and there was no way to switch it on through fluent API. However, when removing a principal entity from either side, EF was smart enough to first send a SQL delete statement to get rid of the dependent record on the link table and then send another delete to remove the principal record. 

    In CTP5 there is still no way to enable cascade deletes in a Many-to-Many association by default, and also EF does not remove the dependent record automatically anymore. As a result when I try to remove the principal, I get a SQLException complaining about the dependent record on the link table. I look into pluggable conventions but there is nothing such a ManyToManyCascadeDeleteConvention exists that I could use to enable cascade delets as well.

    I would really appreciate if someone could clarify this.

    Thanks,
    Morteza

    Tuesday, January 4, 2011 6:22 PM

Answers

  • Morteza,

     

    It appears I spoke too soon in my previous response.  I am following up with other people as to whether or not we should be creating the database differently in this case—i.e. adding the cascade delete to the join table or providing an option to do so.  The workaround for now is to load the related entities before performing the delete.

     

    With regard to cascade delete for many-to-many not being supported.  In this case I am referring not to the join table, but to a cascade in the conceptual model that goes from the entity on one side of the relationship all the way to the entities on the other side of the relationship.  For example, you delete a User and all Roles associated with that User are also deleted.  The cascade delete defined in the conceptual model is intended to reflect (not replace) the cascade delete defined in the database.  (Alex James wrote a good article on this here: http://blogs.msdn.com/b/alexj/archive/2009/08/19/tip-33-how-cascade-delete-really-works-in-ef.aspx.) For us to support this approach with many-to-many it would require multiple cascades setup in the database (which I’m not 100% sure are even possible) and additional work in the state manager to keep things in sync.  It’s something that we could look into more, but it seems like the uses for this kind of cascade are probably not very common and hence I doubt it is something we will revisit soon.

     

    Thanks,

    Arthur

    • Marked as answer by Morteza Manavi Thursday, January 6, 2011 3:47 PM
    Wednesday, January 5, 2011 10:05 PM
    Moderator

All replies

  • Morteza,

     

    Cascade delete in not supported in core EF for many-to-many relationships.  I could go into the reasons for this if you are interested, but on re-reading your question it seems like you aren’t really asking about cascade delete, but rather deletion of the row in the link table.  EF should do this automatically and I have not been able to repro the case where it doesn’t.  Could you provide some code where you are getting a many-to-many relationship that doesn’t work in this way?

     

    Thanks,

    Arthur

    Wednesday, January 5, 2011 6:07 PM
    Moderator
  • Morteza,

     

    With some further investigation I was able to reproduce this bug.  This is not a case where you need (or even should) specify a cascade delete using the API.  Instead the delete to the join table should be setup implicitly as part of setting up a many-to-many relationship with Code First.  This is because all many-to-many relationships in Code First should work this way.  I’m not sure right now if there is any workaround for this but I will keep looking.

     

    Thanks,

    Arthur

    Wednesday, January 5, 2011 7:14 PM
    Moderator
  • Arthur,

    First off, thanks for looking into my question, really appreciate your helps :)

    Actually, I was really asking 2 questions, first why there is no way to specify cascade delete in many to many and why "Cascade delete in not supported in core EF for many-to-many relationships" like you mentioned. I am really interested to know the rationals/reasons  behind this.

    The second question was that why EF does not implicitly delete the link table record when you delete the principal and you are right, it's not always reproducible, and I was be able to find the reason:

    Basically, you need to explicitly eager/Lazy load the navigation property and have it loaded on the context when you remove the dependent. For example, consider this model:

    public class User
    {
     public int UserId { get; set; }
     public virtual ICollection<Address> Addresses { get; set; }
    }
    
    public class Address
    {
     public int AddressID { get; set; } 
     public virtual ICollection<User> Users { get; set; }
    }
    

    Assuming that we have a User with an address in the database, this code will throw:

    using (EntityMappingContext context = new EntityMappingContext())
    {
     User user = context.Users.Find(1); 
     context.Users.Remove(user);
     context.SaveChanges();
    }
    

    However, this one will perfectly work with removing the link table's record first:

    using (EntityMappingContext context = new EntityMappingContext())
    {
     User user = context.Users.Find(1); 
    ((IObjectContextAdapter)context).ObjectContext.LoadProperty(user, u => u.Addresses);
     context.Users.Remove(user);
     context.SaveChanges();
    }

    So, is this the default behavior or this a bug which will go away in the RTM and we will be able to remove the User without loading its addresses property?

    Thanks,

    Morteza

     

     

    Wednesday, January 5, 2011 8:34 PM
  • Morteza,

     

    It appears I spoke too soon in my previous response.  I am following up with other people as to whether or not we should be creating the database differently in this case—i.e. adding the cascade delete to the join table or providing an option to do so.  The workaround for now is to load the related entities before performing the delete.

     

    With regard to cascade delete for many-to-many not being supported.  In this case I am referring not to the join table, but to a cascade in the conceptual model that goes from the entity on one side of the relationship all the way to the entities on the other side of the relationship.  For example, you delete a User and all Roles associated with that User are also deleted.  The cascade delete defined in the conceptual model is intended to reflect (not replace) the cascade delete defined in the database.  (Alex James wrote a good article on this here: http://blogs.msdn.com/b/alexj/archive/2009/08/19/tip-33-how-cascade-delete-really-works-in-ef.aspx.) For us to support this approach with many-to-many it would require multiple cascades setup in the database (which I’m not 100% sure are even possible) and additional work in the state manager to keep things in sync.  It’s something that we could look into more, but it seems like the uses for this kind of cascade are probably not very common and hence I doubt it is something we will revisit soon.

     

    Thanks,

    Arthur

    • Marked as answer by Morteza Manavi Thursday, January 6, 2011 3:47 PM
    Wednesday, January 5, 2011 10:05 PM
    Moderator
  • i answered a similar question. if that might help.

     

    http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/3803853b-bca9-4e20-adad-2d5dd45bd3b8/#18f6c0f7-381f-4ffe-ab8e-ea79cdab73c6

     

    http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/11a725e0-7554-4639-a33c-070d6dfb9170/

    The option for cascade i think we need in the ctp5 should set the cascade at teh ssdl layer so that when we generate the database from the model it creates the right cascades at the database layer. This way i do not have to fetch related entities just for the context to mark them as deleted.


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Wednesday, January 5, 2011 10:22 PM
  • Arthur,

    I think automatically deleting one side of the association as a result of other side being removed (e.g. delete a role because a user is removed) is kind of brute force and not considered to be a common use case. What we are looking for is basically:

    1. This bug goes away so that we can remove a User without having to eagerly/lazily load its navigation property.
    2. Something like a ManyToManyCascadeDeleteConvention would be highly appreciated so that we can add it to the conventions and get the cascade setup for us in the DB to preserve referential integrity in the database.

    Thanks,

    Morteza

    Thursday, January 6, 2011 3:45 PM