Entity Framework Core 7 - composite key mapping problem

Question

I am struggling to create the necessary attributes and code to allow me to retrieve all information from 3 tables I have.

The tables are:

Recipe table:

Column	Type
RecipeId	int (Key)
Title	varchar

Ingredients table:

Column	Type
IngredientId	int (Key)
Description	varchar

Ingredients_Mapping table:

Column	Type
RecipeId	int (Key)
IngredientId	int (Key)
Quantity	int (Key)

Hopefully the above makes sense. Each recipe may contain many ingredients. When I've pulled back details before it has been a simple one and I've added a .Include(x => x.Whatever) to extract the data from the joining table.

Here's the code:

public class Recipe
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }

    [NotMapped]
    public Ingredient[] Ingredients { get; set; }
}

public class Ingredient
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
}

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

public async Task<List<Recipe>> GetAllRecipesAsync()
{
    return await _MyDbContext.Recipes
                             .Include(x => x.???)
                             .OrderBy(b => b.Title).ToListAsync();
}

Could somebody please advise how I can do this please?

Guru Stron · Accepted Answer · 2023-04-07T20:09:35.837

4

There are two main ways to configure the many-to-many relationship in EF Core - either by setting two entities up and having EF Core to generate the join table without explicitly exposing it:

public class Recipe
{
    // ...
    public List<Ingredient> Ingredients { get; set; }
}

public class Ingredient
{
    // ...
    public List<Recipe> Recipes { get; set; }
}

Or setting up the join table explicitly, which should be the case taking in account the need to store the additional information in it (Quantity):

public class Recipe
{
    // ...
    public List<RecipeIngredient> RecipeIngredient { get; set; }
}

public class Ingredient
{
    // ...
    public List<RecipeIngredient> RecipeIngredients { get; set; }
}

// for 7th version, otherwise use 
// modelBuilder.Entity<RecipeIngredient>().HasKey(e => new {e.RecipeId,e.IngredientId }) 
[PrimaryKey(nameof(RecipeId), nameof(IngredientId))] 
public class RecipeIngredient
{
    public int RecipeId { get; set; }
    public Recipe Recipe { get; set; }

    public int IngredientId { get; set; }
    public Ingredient Ingredient { get; set; }

    [Required]
    public int Quantity { get; set; }
}

This should already be fully defined relationship but if needed you can apply the configuration via model builder (also see options to set up composite key).

edited Apr 07 '23 at 20:09

answered Mar 04 '23 at 23:27

Guru Stron

102,774
10
95
132

1

Gourou Stron With two primary keys it's not a problem? – Laaziv Mar 04 '23 at 23:49
1

You just need to declare the compound key in your fluent config instead of using `[Key]` attributes, eg `modelBuilder.Entity().HasKey(e => new {e.RecipeId,e.IngredientId });`. And you can have Many-to-many _and_ a linking entity if you want. There's an example in the docs: https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many – David Browne - Microsoft Mar 05 '23 at 00:02
@Laaziv updated code to correctly support composite key – Guru Stron Mar 05 '23 at 00:10
Thank you for the reply but this hasn't worked for me. I have made the changes to the classes as per your code. What should I then enter for the Service call? I have: await _recipesDbContext.Recipes.Include(c => c.IngredientMappings).OrderBy(b => b.Title).ToListAsync(); But this creates the following error below. System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles. – Dan Mar 10 '23 at 22:56
@Dan actually it has. Now you have completely another problem - check out [this](https://stackoverflow.com/a/62985944/2501279) answer. – Guru Stron Mar 11 '23 at 01:37
@GuruStron Ah thanks, that fixed it but another problem appears. My ingredient and recipe objects are returned as null. It must be missing some type of mapping? "recipeIngredients": [ { "recipeId": 4, "recipe": null, "ingredientId: 2, "ingredient": null, "quantity": 8 } ] – Dan Mar 11 '23 at 13:28
This is my service code: _recipesDbContext.Recipes.Include(c => c.Category).Include(x => x.RecipeIngredients).OrderBy(b => b.Title).ToListAsync(); – Dan Mar 11 '23 at 13:43
@Dan it is hard to tell without full repro, I would argue that it is worth of separate question, but be sure to provide full [mre]. – Guru Stron Mar 11 '23 at 14:32

score 1 · Answer 2 · edited Mar 11 '23 at 10:10

Guru Stron's answer is fine but, if you have problem with two primary keys, use this config:

public class Recipe
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public required string Title { get; set; }
        public virtual List<IngredientMapping> IngredientMappings { get; set; }
    }

    public class Ingredient
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public required string Title { get; set; }
        public List<IngredientMapping> ingredientMappings { get; set; }
    }

    public class IngredientMapping
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public int RecipeId { get; set; }        
        [Required]
        public int IngredientId { get; set; }
        [Required]
        public int Quantity { get; set; }
        public virtual required Recipe Recipe { get; set; }
        public virtual required Ingredient Ingredient { get; set; }        
    }

score 1 · Answer 3 · answered Apr 02 '23 at 20:37

Apologies for not responding earlier, I've had family things to take care of. I got this working with a mixture of the above answers and some reading up and trial and error. Hopefully this will help somebody else who was in my position, here is my finished code:

public class Ingredient
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    [MaxLength(50)]
    public string Title { get; set; }
}

[PrimaryKey(nameof(RecipeId), nameof(IngredientId))]
public class RecipeIngredient
{
    public int RecipeId { get; set; }
    public int IngredientId { get; set; }
    public Ingredient Ingredient { get; set; }

    [Required]
    public int Quantity { get; set; }
}

public class Recipe
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    [MaxLength(100)]
    public string Title { get; set; }   
    public List<RecipeIngredient> RecipeIngredients { get; set; }
}

Entity Framework Core 7 - composite key mapping problem

3 Answers3

Linked