Entity Framework Core 7复合键映射问题：多表关联查询配置求助

要实现食谱与食材的关联查询，核心是正确配置实体间的导航关系（因为中间表Ingredients_Mapping包含额外字段Quantity，无法使用EF自动生成的隐式多对多关系，必须显式配置中间实体）。以下是分步实现步骤：

1. 修正实体类的映射与导航属性 #

首先调整实体类，让EF能识别表间关联：

Recipe 类 #

public class Recipe
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("RecipeId")] // 对应数据库列名RecipeId
    public int Id { get; set; }
    
    [Required]
    public string Title { get; set; }

    // 导航到中间表的集合
    public ICollection<IngredientMapping> IngredientMappings { get; set; } = new List<IngredientMapping>();
}

Ingredient 类 #

public class Ingredient
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("IngredientId")] // 对应数据库列名IngredientId
    public int Id { get; set; }
    
    [Required]
    [Column("Description")] // 对应数据库列名Description（原实体用Title，需匹配数据库字段）
    public string Title { get; set; }

    // 导航到中间表的集合
    public ICollection<IngredientMapping> IngredientMappings { get; set; } = new List<IngredientMapping>();
}

IngredientMapping 类 #

public class IngredientMapping
{
    [Key]
    [Column("RecipeId")]
    public int RecipeId { get; set; }
    
    [Key]
    [Column("IngredientId")]
    public int IngredientId { get; set; }
    
    [Required]
    public int Quantity { get; set; }

    // 导航到Recipe实体
    [ForeignKey(nameof(RecipeId))]
    public Recipe Recipe { get; set; }

    // 导航到Ingredient实体
    [ForeignKey(nameof(IngredientId))]
    public Ingredient Ingredient { get; set; }
}

2. 配置DbContext（可选，用Fluent API增强可读性） #

如果偏好Fluent API而非数据注解，可在DbContext的OnModelCreating方法中添加以下配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置中间表的复合主键
    modelBuilder.Entity<IngredientMapping>()
        .HasKey(m => new { m.RecipeId, m.IngredientId });

    // 配置Recipe与IngredientMapping的一对多关系
    modelBuilder.Entity<IngredientMapping>()
        .HasOne(m => m.Recipe)
        .WithMany(r => r.IngredientMappings)
        .HasForeignKey(m => m.RecipeId);

    // 配置Ingredient与IngredientMapping的一对多关系
    modelBuilder.Entity<IngredientMapping>()
        .HasOne(m => m.Ingredient)
        .WithMany(i => i.IngredientMappings)
        .HasForeignKey(m => m.IngredientId);
}

3. 修改查询方法，加载关联数据 #

现在可以通过Include+ThenInclude加载完整的关联数据：

public async Task<List<Recipe>> GetAllRecipesAsync()
{
    return await _MyDbContext.Recipes
                             .Include(r => r.IngredientMappings)
                                 .ThenInclude(m => m.Ingredient) // 加载中间表关联的食材
                             .OrderBy(r => r.Title)
                             .ToListAsync();
}

4. （可选）投影为DTO返回 #

如果需要返回更简洁的结构（比如直接包含食材列表及对应数量），可以使用LINQ投影到自定义DTO：

public class RecipeDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<IngredientWithQuantityDto> Ingredients { get; set; }
}

public class IngredientWithQuantityDto
{
    public int Id { get; set; }
    public string Description { get; set; }
    public int Quantity { get; set; }
}

public async Task<List<RecipeDto>> GetAllRecipesWithIngredientsAsync()
{
    return await _MyDbContext.Recipes
                             .Select(r => new RecipeDto
                             {
                                 Id = r.Id,
                                 Title = r.Title,
                                 Ingredients = r.IngredientMappings
                                     .Select(m => new IngredientWithQuantityDto
                                     {
                                         Id = m.Ingredient.Id,
                                         Description = m.Ingredient.Title,
                                         Quantity = m.Quantity
                                     }).ToList()
                             })
                             .OrderBy(dto => dto.Title)
                             .ToListAsync();
}

内容的提问来源于stack exchange，提问作者Dan