I try to create the following database design with EF Core 3.1 (code-first)
- Entity "Recipe" can have a list of type "Resource" and a single type of "NutritionFacts"
- Entity "Ingredient" can have a single "NutritionFacts"
- Entity "Instruction" can have a list of type "Resource"
But I found no way to implement this without having multiple "NutritionFacts" or "Resource" tables. (RecipeNutritionFacts-/IngredientNutritionFacts) And I also don't want to blow up my "Recipe" or "Ingredient" tables with the columns from the "NutritionFacts-/Resource" entities. (owned types)
Goal: I would like to reuse the tables "Resource" and "NutritionFacts" in multiple entities.
If I delete a "Resource" from the collection in entity "Recipe" or "Instrucion" then the corresponding "Resource" entity should be also deleted. (recipe.Resource.remove(x))
Same for a "NutritionFacts" from entity "Recipe" or "Ingredient" (recipe.NutritionFacts = null)
I already tried several combinations with owned types, etc... but with no success. Any ideas for a good implementation to reach this goal?
Example Classes:
public class NutritionFacts
{
public int NutritionFactsId { get; set; }
public decimal Kcal { get; set; }
public decimal Fat { get; set; }
}
public class Resource
{
public int ResourceId { get; set; }
public string Path { get; set; }
}
public class Ingredient
{
public int IngredientId { get; set; }
public string Title { get; set; }
public virtual NutritionFacts NutritionFacts { get; set; }
}
public class Recipe
{
public int RecipeId { get; set; }
public string Title { get; set; }
public virtual NutritionFacts NutritionFacts { get; set; }
public virtual ICollection<Resource> Resources { get; set; }
}
public class Instruction
{
public int InstructionId { get; set; }
public string Title { get; set; }
public virtual ICollection<Resource> Resources { get; set; }
}
Example Context:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// variant-1 owned
modelBuilder.Entity<Ingredient>().OwnsOne(x => x.NutritionFacts).HasKey(r => r.UniqueIdentifier);
modelBuilder.Entity<Entities.Recipe>().OwnsOne(x => x.NutritionFacts).HasKey(r => r.UniqueIdentifier);
// variant-2
modelBuilder.Entity<Entities.Recipe>()
.HasOne(p => p.NutritionFacts).WithOne();
modelBuilder.Entity<Ingredient>()
.HasOne(p => p.NutritionFacts).WithOne();
}