locked
"Code First" problem - "may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION" RRS feed

  • Question

  • User-1851576699 posted

    Im creating my databases using "Code First" C# model classes:

    class WishList {
    
        public int WishListId { get; set; }
    
        public System.String WishListName { get; set; }
    
        public int Product1Id { get; set; }
    
        [ForeignKey("Product1Id")]
    
        public Product Product1 { get; set; }
    
        public int Product2Id { get; set; }
    
        [ForeignKey("Product2Id")]
    
        public Product Product2 { get; set; }
    
    }

    ** RESOLVED ** See last edit comment below.

    Okay, so the problem here is that when/if a "Product" gets deleted from the "Products" table the cascade action on the "Products" table is defaulting to delete the dependent entities which are the "WishList" types that would still be referencing the "Product" after it is deleted. I thought by simply omitting the [Required] data annotation attribute for the two "Product" references(and foreign keys that back those references) in my "WishList" class the default action for deleting a "Product" in table "Products" would be "ON DELETE NO ACTION". That is apparently not the default case here. Can somebody guide me on the best way to resolve this issue?

    My "WishList" class above is trying to add multiple foreign key constraints to the "WishList" table. I can reference one "Product" with no problem but when I add a second "Product" reference it faills. How do I specify "ON DELETE NO ACTION" for my "Products" table using "Code First" classes. Is that possible? More specifically, I guess I need to modify the delete action of the foreign key constraints that are being added to the "Products" table so that they are "ON DELETE NO ACTION" specifically.

    Or, how do I keep both my foreign key references to "Product" types in my "WishList" class without creating foreign key constraints? Are two references of the same type in a single entity a bad design decision?

    Thanks for reading.

    edit #: You are probably wondering why I just wouldn't use a System.Collections.Generic.List<Product> type for my 2 references and remove the 2 navigation properties. That will not solve my issue. I am going for something more specific. So that the first reference "Product1" would be like "Most Wanted" product and second reference"Product2" would be next most wanted. So please let me preempt the recommendation to use a List for my scenario. I only require a reference to two products, no more than that, and the order has meaning.

    edit #: All I had to do to solve my problem was declare both foreign keys in my model as nullable types with the question mark (Guid ?). Made this nasty error go away. So, I still get the navigational functionality that I wanted. Only issue I am left with no is "WishList" records piling up in my "WishList" table if both Independent references get deleted in the "Products" table. Not a big deal I guess.

    Saturday, September 21, 2013 6:54 AM

All replies