CODEWITHSUNDEEP
c#databaseentity-framework-core
joa77
joa77

Reputation: 125

How to create EF Core navigation property with complex multilevel relationship?

I'm currently learning EF Core and having a hard time writing efficient queries and defining my models properly.

I have this simplified database scheme and currently I'm struggling with creating a navigation property called Foods on the Recipe entity. Is there a chance to do this, without creating an extra table, which tracks the associated food entities?

  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐         ┌──────────────┐
  │    recipe    │         │     step     │         │  ingredient  │         │     food     │
  ├──────────────┤         ├──────────────┤         ├──────────────┤         ├──────────────┤
  │- Id          │   1:n   │- Id          │   1:n   │- Id          │   n:1   │- Id          │
  │              ├─────────┤- RecipeId    ├─────────┤- StepId      ├─────────┤              │
  │              │         │              │         │- FoodId      │         │              │
  │              │         │              │         │- Quantity    │         │              │
  │              │         │              │         │              │         │              │
  │              │         │              │         │              │         │              │
  └──────────────┘         └──────────────┘         └──────────────┘         └──────────────┘

To clarify the model:

So first question is: Can I use the ModelBuilder in my DbContext to describe this relationship and make it navigateable via a ICollection<Food> Food property inside the recipe class?

Second questions if the first one is possible: Will the Foods property contain the same food multiple times, when it is in multiple ingredients of the same recipe?

Thanks in advance!

EDIT

As David Browne pointed out, this feature is currently not supported in EF core.

So I got a new question: Is it possible to define a ICollection<Ingredients> property on the recipe entity, which uses the already existing relationship definitions?

I already thought of:

modelBuilder.Entity<Recipe>()
  .HasMany(recipe => recipe.Ingredients)
  .WithOne(ingredient => ingredient.Recipe)
  .UsingEntity<Step>();

But this won't work

Upvotes: 1

Views: 800

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

What you're asking for is called a "Direct Many-to-Many Relationship", but with two levels of linking tables. This is a mapping scenario that EF doesn't (yet) support. See Relationshps - EF Core - Many to Many

You can always use "Indirect Many-to-Many" with the existing relationships.

from r in db.Recipes
select new 
{ 
  Recipe=r, 
  Foods = r.Steps.SelectMany(s => s.Ingredients).Select(i => i.Food).ToList()
}

Upvotes: 2

Related Questions