How can I get data from a many-to-many relationship in .NET?

Question

I made an API for learning using .NET.

This is a simple API where I have Pizzas and Ingredients. I want to get all the pizzas in the database with their ingredients. Something Like this:

[
  {
    "id": 2,
    "name": "Pizza XYZ",
    "price": 4.5,
    "isPizzaOfTheWeek": false,
    "amount": 15,
    "pizzaIngredients": [
      {
         id: 1,
         name: 'Onion',
         price: '2.00',
         cost: '8.00'
      }
    ]
  },
]

My entities are these:

public class Pizza
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    [Column(TypeName = "decimal(5,2)")]
    public decimal Price { get; set; }      
    public bool IsPizzaOfTheWeek { get; set; }
    public int Amount { get; set; }
    
    public List<PizzaIngredient> PizzaIngredients { get; set; } = null!;
}

public class Ingredient
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    
    [Column(TypeName = "decimal(5,2)")]
    public decimal Price { get; set; }      // Price per 100 g

    public List<PizzaIngredient> PizzaIngredients { get; set; } = null!;
}

public class PizzaIngredient
{
    [Key]
    public int PizzaId { get; set; }
    public Pizza Pizza { get; set; } = null!;
    
    [Key]
    public int IngredientId { get; set; }
    public Ingredient Ingredient { get; set; } = null!;
    
    [Column(TypeName = "decimal(5,2)")]       
    public decimal Cost { get; set; } // Total Cost of this ingredient for this pizza
}

The problem is that I don't know how to do this.

I try using : var pizzas = await _context.Pizza.Include(p => p.PizzaIngredients).ThenInclude(pi => pi.Ingredient).ToListAsync();

This function brings me all the ingredients data but it's also bringing me repetitive data because "ingredients" have property which is also a List of PizzaIngredients.

I hope everything is clear. If more information is needed, I will write it.

Answer 1

Usually I advise to remove the mapping table if there is no need to have additional properties in it. Move the price to the pizza because it applies to it. Unless you make a order table. Ypu don't need to do anything in FluentApi for that many-to-many

Models

public class Pizza
{
    public Pizza()
    {
       //Initialize the collection in Hashset for fast search
       this.Ingredients = new HashSet<Ingredients>();
    }

    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    [Column(TypeName = "decimal(5,2)")]
    public decimal Price { get; set; }      
    public bool IsPizzaOfTheWeek { get; set; }
    public int Amount { get; set; }
    public decimal Cost { get; set; }
    
    //Use a collection instead of a list to be able to convert it to HashSet
    public virtual ICollection<Ingredient> Ingredients { get; set; }
}

public class Ingredient
{
    public Ingredient()
    {
       //Initialize the collection in Hashset for fast search
       this.Pizzas = new HashSet<Pizza>();
    }

    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    
    [Column(TypeName = "decimal(5,2)")]
    public decimal Price { get; set; }      // Price per 100 g

    //Use a collection instead of a list to be able to convert it to HashSet
    public virtual ICollection<Pizza> Pizzas { get; set; }
}

//DtoModel
public class PizzaDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }      
    public bool IsPizzaOfTheWeek { get; set; }
    public int Amount { get; set; }
    public decimal Cost { get; set; } //Total cost of ingredients prices
    
    public List<Ingredient> Ingredients { get; set; };
}

Service

//Qerry
//All pizza ids you want with single query
var ids = new List<int> { 1, 4, 6, 22 };

var pizzas = await this.context.Pizzas
    .Where(x => ids.Contains(x.Id)) // Pizzas by ids
    .Where(x => x.Ingredients.Any(x => x.Name == name)) //Pizzas by ingredient
    .Select(x => new PizzaDto
    {
       Id = x.Id,
       Name = x.Name,
       Price = x.Price,
       IsPizzaOfTheWeek = x.IsPizzaOfTheWeek,
       Amount = x.Amount ,
       Cost = x.Ingredients.Sum(x => x.Price),
       Ingredients = x.Ingredients,
    }).ToListAsync();


//Save
var pizza = new Pizza
{
   Name = name,
   Price = price,
   IsPizzaOfTheWeek = isPizzaOfTheWeek,
   Amount = amount ,
   Cost = ingredients.Sum(x => x.Price),
   Ingredients = ingredients, //List of ingreadients
};

await this.context.AddAsync(pizza);
await this.context.SaveChangesAsync();