How to use CASCADE DELETE for removing all tenant data

I want to remove all the entities related to a tenant from the a multi-tenant database when one tenant is removed. I’m working with EF Core and Microsoft SQL Server, although I guess this topic is not limited to those technologies.

In this answer, it is said that:

this is the classical case for use of FOREIGN KEYS with ON CASCADE option

However, I’ve not managed to make it work. It seems that since the tenantized entities are also linked between them, cycles would be created. I’ve created a simple app to test this in isolation, you can find it here. When trying to update the database according to the migration, I get the following error:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint ‘FK_Posts_Tenants_TenantId’ on table ‘Posts’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

My DbContext and entities:

public class BloggingContext : DbContext
{
    public DbSet<Tenant> Tenants { get; set; }
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlServer("Server=.;Database=TestCascadeDelete;Trusted_Connection=True");
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>(b => b.HasOne<Tenant>().WithMany().HasForeignKey(e => e.TenantId));
        modelBuilder.Entity<Post>(b => b.HasOne<Tenant>().WithMany().HasForeignKey(e => e.TenantId));
    }
}
public class Tenant
{
    public int TenantId { get; set; }
}
public class Blog
{
    public int TenantId { get; set; }
    public int BlogId { get; set; }
    public List<Post> Posts { get; } = new List<Post>();
}
public class Post
{
    public int TenantId { get; set; }
    public int PostId { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

So my question is: is this possible? and if it is, what should I change in my configuration? I’ve read that making the FK’s between entities nullable could solve the problem, but I don’t want to make that change just to make this work. I’ve also seen this answer for replacing the CASCADE DELETE with a trigger, but I don’t like this option.

Answer

You have “multiple cascade paths” for Post:

Tenant => Blog => Post
Tenant => Post

Post.TenantId is redundant as a Post is contained in a tenant-specific Blog.

There are various possibilities:

remove Post.TenantId
remove the reference between Tenant and Post (replace by Blog and Post)
add correct OnDelete() behavior (MS docs)

Advertisement

Answer