Cascade delete in one to one relationship

Question

I want to have cascade delete in 1:1 relationship, where i reference multiple entities to one. Problem is throws me an error on database update

Introducing FOREIGN KEY constraint 'FK_dbo.CategoryArticles_dbo.Articles_Article_Id' on table 'CategoryArticles' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

RoutingSeo entity is for storing seo friendly url in database for later usage. My problem is clearly M:N relationship between Article and Category. Is there something how can I deal with this problem?

Here are my entities of my model

public class Article : IEntity<int>
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Category> Categories { get; set; }

    [Required]
    public virtual RoutingSeo RoutingSeo { get; set; }
    public int RoutingSeoId { get; set; }

}

public class Category : IEntity<int>
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Article> Articles { get; set; }

    [Required]
    public virtual RoutingSeo RoutingSeo { get; set; }
    public int RoutingSeoId { get; set; }
}

public class SpecificProduct : IEntity<int>
{
    public int Id { get; set; }

    public string Name { get; set; }

    [Required]
    public RoutingSeo RoutingSeo { get; set; }        
    public int RoutingSeoId { get; set; }
}

public class RoutingSeo : IEntity<int>
{
    public int Id { get; set; }

    public string SeoRoute { get; set; }

    public Article Article { get; set; }
    public SpecificProduct SpecificProduct { get; set; }
    public Category Category { get; set; }
}

Here is my fluent api code where i specify cascade delete

modelBuilder.Entity<Article>()
    .HasRequired(x => x.RoutingSeo)
    .WithOptional(x=>x.Article)
    .WillCascadeOnDelete();

modelBuilder.Entity<Category>()
    .HasRequired(x => x.RoutingSeo)
    .WithOptional(x=>x.Category)
    .WillCascadeOnDelete();

modelBuilder.Entity<SpecificProduct>()
    .HasRequired(x => x.RoutingSeo)
    .WithOptional(x=>x.SpecificProduct)
    .WillCascadeOnDelete();

Answer 1

You are right, it is your many-to-many relation ship between Article and Category: one Article has zero or more Categories and every Category may be used by zero or more Articles.

If you delete an Article, its Categories can't be deleted automatically, because the Category might be used by other Articles, and even if it isn't used right now, entity framework doesn't know whether you want to use it tomorrow. After all, you specified that every Category might be used by zero or more Articles.

Similarly, if you remove a Category, entity framework can't automatically remove the Articles belonging to this category.

This differs from a one-to-many relationship. For example, if you have a one-to-many relationship of a Book and its Pages, then every Book has zero or more Pages and every Page belongs to exactly one Book.

If you remove the Book, then entity framework knows that it should automatically remove all Pages of the Book, which are all Pages with a foreign key BookId. If Entity Framework would only remove the Book, then we would have a bunch of Pages with foreign key value pointing to a non-existing Book. So in one-to-many relations, entity framework can cascade on delete.

Alas, in many-to-many this is not possible.

On the bright side, you have the advantage that you can delete the last Article of a Category, and keep the Category intact. Tomorrow you can add a new Article that uses this Category.

So if you want to remove an article, you manually have to remove it from the 'Categories` it uses:

many-to-many following the standard naming conventions:

class Article
{
    public int Id {get; set;}
    // an Article belongs to zero or more Categories:
    public virtual ICollection<Category> Categories {get; set;}
    ...
}
class Category
{
    public int Id {get; set;}
    // a Category is used by zero or more Articles:
    public virtual ICollection<Article> Articles{get; set;}
    ...
}

Don't forget to declare your ICollections virtual!

class MyDbContext : DbContext
{
    public class DbSet<Article> Articles {get; set;}
    public class DbSet<Category> Categories {get; set;}
}

You don't have to mention the junction-table, entity framework will make it automatically for you, but you won't have to use it for joins if you want Articles with their Categories, or Categories with their Articles, just use the ICollections

Note: As Categories is not the expected plural of Category, you'll have to tell entity framework the proper table name. Out of scope of this question.

Delete an Article, but keep all Categories it belongs to alive:

using (var dbContext = new MyDbContext(...))
{
    Article articleToRemove = ...
    dbContext.Articles.Remove(articleToRemove);
    dbContext.SaveChanges();
}

Entity framework will automatically perform the proper joins, and remove the articleToRemove from every Category. However, the Categories won't be removed.

In fact, internally the Categories table doesn't change at all. All records with Article.Id will be removed from the junction table.