ASP.NET Core MVC中菜品与配料多对多关联查询问题

我正在开发一款餐厅应用，拥有Dish实体和Ingredient实体，二者需建立关联以了解每道菜品的配料。我计划创建包含DishId和IngredientId的关联实体DishIngredient来处理菜品-配料组合，实体代码如下：

public class DishIngredient : AuditableBaseEntity
{
    public int DishId { get; set; }
    public Dish Dish { get; set; }
    public int IngredientId { get; set; }
    public Ingredient Ingredient { get; set; }
}

我尝试在控制器中创建方法，通过DishViewModel的导航属性List<Ingredient> Ingredients获取所有菜品及对应配料，但不确定实现是否正确。DishViewModel代码如下：

public class DishViewModel
{
     public int Id { get; set; }
     public string Name { get; set; }
     public double Price { get; set; }
     public int People { get; set; }
     public string Category { get; set; }
     public List<Ingredients> Ingredients { get; set; }
}

我的问题是不清楚如何同时获取菜品及其配料。我尝试用EF Core的Include方法加载DishIngredients表以获取Ingredients数据，但未返回任何数据（推测是缺少过滤导致DishIngredients表数据为null），代码如下：

public async Task<List<Dish>> GetDishesWithIngredients()
{
     var result = _dbContext.Dishes
                            .Include(di => di.DishIngredients)                
                            .ToList();
     return result;            
}

1. 补全实体导航属性配置 #

首先要确保Dish和Ingredient实体中定义了与DishIngredient的关联导航，否则EF Core无法识别多对多关系：

public class Dish
{
    // 原有属性
    public ICollection<DishIngredient> DishIngredients { get; set; } = new List<DishIngredient>();
}

public class Ingredient
{
    // 原有属性
    public ICollection<DishIngredient> DishIngredients { get; set; } = new List<DishIngredient>();
}

2. 正确加载嵌套关联数据 #

你的代码仅加载了DishIngredients，但未进一步关联Ingredient实体，需要用ThenInclude嵌套加载关联数据，同时改用异步方法的标准实现：

public async Task<List<Dish>> GetDishesWithIngredients()
{
     var result = await _dbContext.Dishes
                            .Include(d => d.DishIngredients)
                                .ThenInclude(di => di.Ingredient) // 加载DishIngredient对应的配料实体
                            .ToListAsync();
     return result;            
}

3. 映射到DishViewModel（修正拼写错误） #

注意你的DishViewModel中List<Ingredients>是拼写错误，应该改为List<Ingredient>。之后可以手动映射或使用AutoMapper将实体转换为ViewModel：

public async Task<List<DishViewModel>> GetDishesWithIngredientsViewModel()
{
     var dishes = await _dbContext.Dishes
                            .Include(d => d.DishIngredients)
                                .ThenInclude(di => di.Ingredient)
                            .ToListAsync();

     var viewModel = dishes.Select(d => new DishViewModel
     {
         Id = d.Id,
         Name = d.Name,
         Price = d.Price,
         People = d.People,
         Category = d.Category,
         Ingredients = d.DishIngredients.Select(di => di.Ingredient).ToList()
     }).ToList();

     return viewModel;
}

4. 排查数据为空的额外可能 #

• 检查DishIngredient表是否有有效数据，确保DishId和IngredientId都关联了数据库中存在的记录。
• 如果使用EF Core旧版本，需要在上下文的OnModelCreating中显式配置多对多关系：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<DishIngredient>()
        .HasKey(di => new { di.DishId, di.IngredientId });

    modelBuilder.Entity<DishIngredient>()
        .HasOne(di => di.Dish)
        .WithMany(d => d.DishIngredients)
        .HasForeignKey(di => di.DishId);

    modelBuilder.Entity<DishIngredient>()
        .HasOne(di => di.Ingredient)
        .WithMany(i => i.DishIngredients)
        .HasForeignKey(di => di.IngredientId);
}

内容的提问来源于stack exchange，提问作者edwin.ill