Hi! I need to make a conclusion with DB using EF. The user adds the products that he has in the refrigerator one by one by clicking on the "Add product" button. These products are added to the array. List<string> productsList = new List<string>(); and I plan on sorting out this array to output recipes in which there are these products. But the catch is that there is a table with Products and a table with Recipes, and since one product can have many recipes and there can be many products in recipes I link them through the third Table RecipieIngredients in which they are linked by their id. The problem is that I can not understand how I can implement a search for recipes by product and then display a list of recipes?

enter image description here Ingredients

 namespace WindowsFormsApp1 { using System; using System.Collections.Generic; public partial class Ingredients { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Ingredients() { this.RecipeIngredient = new HashSet<RecipeIngredient>(); } public int Id { get; set; } public string Name { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<RecipeIngredient> RecipeIngredient { get; set; } } }

Recipe

 namespace WindowsFormsApp1 { using System; using System.Collections.Generic; public partial class Recipe { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Recipe() { this.RecipeIngredient = new HashSet<RecipeIngredient>(); } public int Id { get; set; } public string Name { get; set; } public Nullable<int> Prop_Time { get; set; } public string Instructions { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<RecipeIngredient> RecipeIngredient { get; set; } } }

RecipeIngredient

 namespace WindowsFormsApp1 { using System; using System.Collections.Generic; public partial class RecipeIngredient { public int Id { get; set; } public int RecipeId { get; set; } public int IngredientId { get; set; } public virtual Ingredients Ingredients { get; set; } public virtual Recipe Recipe { get; set; } } }
  • Have you thought well about the structure of the class recipe? Try to climb on the sites with recipes, see what data indicate in the recipes, those recipes that I saw in your structure will not work. Is your project for learning a language / platform / other? - Bald

1 answer 1

Like, sql would look like this:

 SELECT Name, Prop_Time, Instructions FROM Recipe WHERE Id in ( SELECT RecipeID FROM RecipeIngredient WHERE IngredientId = IDΠΈΡΠΊΠΎΠΌΠΎΠ³ΠΎΠ˜Π½Π³Ρ€ΠΈΠ΄ΠΈΠ΅Π½Ρ‚Π° )

respectively Linq:

 //структуры Π΄Π°Π½Π½Ρ‹Ρ… DbSet<Ingredient> ingrs; DbSet<Recipe> recs; DbSet<RecipeIngredients> recIngrs; //Ρ‚ΠΎΠ³Π΄Π° int[] ingrIDs = { 0, 1, 42, 100500 };//список ID_ΠΈΠ½Π³Ρ€Π΅Π΄ΠΈΠ΅Π½Ρ‚ΠΎΠ², ΠΈΠΌΠ΅ΡŽΡ‰ΠΈΡ…ΡΡ Π² Ρ…ΠΎΠ»ΠΎΠ΄Ρ€ΠΈΠ»ΡŒΠ½ΠΈΠΊΠ΅ var recIDs = recIngrs.Where(ri => (ingrIDs.Contains(ri.IngredientId))).Select(ri => ri.Id);//ID Ρ€Π΅Ρ†Π΅ΠΏΡ‚ΠΎΠ² с Π΄Π°Π½Π½Ρ‹ΠΌΠΈ ΠΈΠ½Π³Ρ€Π΅Π΄ΠΈΠ΅Π½Ρ‚Π°ΠΌΠΈ var recs4ingr = recs.Where(r => (recIDs.Contains(r.Id))).Select(r => (new { r.Name, r.Prop_Time, r.Instructions}));//внятная информация ΠΏΠΎ Ρ€Π΅Ρ†Π΅ΠΏΡ‚Π°ΠΌ с Π΄Π°Π½Π½Ρ‹ΠΌΠΈ ΠΈΠ½Π³Ρ€Π΅Π΄ΠΈΠ΅Π½Ρ‚Π°ΠΌΠΈ

something like, in general :)