Entity Framework Core shared table with cascade delete

Question

I try to create the following database design with EF Core (code-first)

1. Entity "Recipe" can have a list of type "Resource"
2. Entity "Shop" can have a single "Resource"
3. Entity "InstructionStep" can have a list of type "Resource"

If I delete a resource from the "Recipe", "InstructionStep" (collections) or from the "Shop" (single-property) then the corresponding "Resource" entity should be also deleted. (Cascade Delete)

I already tried several things with and without mapping tables but none of my approach was successful. Another idea was to have a property "ItemRefId" in the "Resource" entity to save the "RecipeId/ShopId/InstructionStepId" but I don't get it to work...

Example Classes:

    public class Recipe
    {
        public int RecipeId { get; set; }

        public string Title { get; set; }

        public ICollection<RecipeResource> Resources { get; set; } = new List<RecipeResource>();
    }

    public class Shop
    {
        public int ShopId { get; set; }
        public string Title { get; set; }
        public Resource Logo { get; set; }
    }

    public class Resource
    {
        public int ResourceId { get; set; }
        public string Path { get; set; }
        public int ItemRefId { get; set; }
    }

    public class InstructionStep
    {
        public string InstructionStepId { get; set; }
        public string Title { get; set; }
        public ICollection<RecipeResource> Resources { get; set; } = new List<RecipeResource>();
    }

Any suggestions? Many thanks in advance.

Answer 1

That's not cascade delete. Cascade delete would be when a Recipe is deleted, all of the related Resources are deleted as well.

In EF Core 3, you can use Owned Entity Types for this. The generated relational model is different from what you are proposing, in that Recipe_Resource and InstructionStep_Resource will be seperate tables, and Shop.Logo will be stored in columns on the Shop table. But that's the correct relational model. Having one Resource table with some rows referencing a Recipe and some rows referencing an InstructionStep is a bad idea.

This scenario is sometimes called a "Strong Relationship" where the identity of the related entity is dependent on the main entity, and should be implemented in the relational model by having the the Foreign Key columns be Primary Key columns on the dependent entity. That way there's no way remove a Recipe_Resource without deleting it.

eg

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Linq;

namespace EfCore3Test
{

    public class Recipe
    {
        public int RecipeId { get; set; }

        public string Title { get; set; }

        public ICollection<Resource> Resources { get; } = new List<Resource>();
    }

    public class Shop
    {
        public int ShopId { get; set; }
        public string Title { get; set; }
        public Resource Logo { get; set; }
    }

    public class Resource
    {
        public int ResourceId { get; set; }
        public string Path { get; set; }
        public int ItemRefId { get; set; }
    }

    public class InstructionStep
    {
        public string InstructionStepId { get; set; }
        public string Title { get; set; }
        public ICollection<Resource> Resources { get; } = new List<Resource>();
    }

    public class Db : DbContext
    {
        public DbSet<Recipe> Recipes { get; set; }
        public virtual DbSet<Shop> Shops { get; set; }
        public virtual DbSet<InstructionStep> InstructionSteps { get; set; }

        private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
            {
                builder.AddFilter((category, level) =>
                   category == DbLoggerCategory.Database.Command.Name
                   && level == LogLevel.Information).AddConsole();
            });
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(loggerFactory)
                          .UseSqlServer("Server=.;database=EfCore3Test;Integrated Security=true", 
                                        o => o.UseRelationalNulls());

            base.OnConfiguring(optionsBuilder);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Shop>().OwnsOne(p => p.Logo);
            modelBuilder.Entity<InstructionStep>().OwnsMany(p => p.Resources);
            modelBuilder.Entity<Recipe>().OwnsMany(p => p.Resources);
        }
    }

    class Program
    {

        static void Main(string[] args)
        {
            using var db = new Db();

            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();

            var r = new Recipe();
            r.Resources.Add(new Resource() { ItemRefId = 2, Path = "/" });

            db.Recipes.Add(r);
            db.SaveChanges();

            r.Resources.Remove(r.Resources.First());

            db.SaveChanges();

            var s = new Shop();
            s.Logo = new Resource { ItemRefId = 2, Path = "/" };
            db.Shops.Add(s);
            db.SaveChanges();

            s.Logo = null;
            db.SaveChanges();
        }
    }
}