Get data from three tables by ID C# APi

Question

I am building a project of editing an existing recipe

Please note--- Each recipe can have more than one ingredients group (-this is my point in building this project)

1. Recipe table (recipe ID, name, etc.)
2. Linking table of ingredients group for each recipe ChartsForOneRecipe (group ID, name of the group example: (to base,to the sauce ))
3. There is Ingredients table (ID, and name)
4. A table which has a List of ingredients for all the ingredients groups ingredientsInChart (ID for every ingredient , ID to which ingredient group it belongs , ingredient ID, quantity,)

Does anyone have an idea how do I get out the names of the ingredients associated with one ingredient group ? In C # MVC of course not in SQL

my questions are:

how can I do this in one function ? how to write it correctly that it will work. if it's possible not to copy or create new tables.

Note I dont have any FK in my tables yet. By the end I need to get from db the ingredients groups for each recipe. and all the ingredients groups are in one table and have only the ID of the ingredients', Id of the groups and not the name of it.

I have started Wrote this functions :

in Recipes controller:

mydata db = new data()
private List<ChartsForOneRecipe> lchart;

1. A function that returns all the groups which belongs to a specific recipe the func works well

    [HttpGet]
    public IHttpActionResult GetTheNamesOfChartsByRecipeId(int RecipeId)
    {
   
        lchart = db.ChartsForOneRecipe.Where(ch => ch.recipeId == RecipeId).ToList();
   
        return base.Ok(lchart);
   
    }
   

2. A function that returns all the ingredients in a specific group of ingredients and it didn't work

    [HttpGet]//return all ingredients in a chart by getting chartid 
    public IHttpActionResult GetAlltheIngredientsInChart(int ChartId)
    {
   
        var q = from ing in db.Ingredients
                from ingch in db.IngredientsInChart
                where (ing.ingredientID == ingch.ingredientsInChartId && ingch.chartId == ChartId)
                select new
                { ing.ingredientID, ing.name };
   
        return Ok(q);
   

}

Answer 1

Considering you are using EF6 Code First and you defined your models with their respective Data Annotation to setup your keys, as follow:

public class Ingredients
{
    [Key]
    public int IngredientId { get; set; }
    public string Name { get; set; }
    
    //Begin Other irrelevant attributes
    //...
    //End irrelevant attributes
    
    //Navigation property - THIS IS IMPORTANT
    [ForeignKey("IngredientsInChartId")]
    public ICollection<IngredientsInChart> IngredientsInChart { get; set; }
}

public class IngredientsInChart
{
    [Key]
    public int ChartId { get; set; }
    public string Name { get; set; }
    
    //Begin Other irrelevant attributes
    //...
    //End irrelevant attributes
    
    //Optional, on using FLUENT-API for seeding data (Migration)
    public int IngredientsInChartId { get; set; }
    
    //Navigation property - THIS IS IMPORTANT
    [ForeignKey("IngredientsInChartId")]
    public Ingredients Ingredient { get; set; }
}

Or you may rather use FluentAPI, setting up your context like this:

public class MyData : DbContext
{
    public virtual DbSet<ChartsForOneRecipe> ChartsForOneRecipe { get; set; }
    public virtual DbSet<Recipe> Recipe { get; set; }
    
    //Other irrelevant DBSet properties

    public MyData(DbContextOptions<PharmaDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Primary keys

        modelBuilder.Entity<Ingredients>()
                    .Property(c => c.IngredientId)
                    .UseIdentityAlwaysColumn();        

        modelBuilder.Entity<IngredientsInChart>()
                    .Property(c => c.ChartId)
                    .UseIdentityAlwaysColumn();        

        //Foreign keys
        modelBuilder.Entity<Ingredients>()
                    .HasMany(c => c.IngredientsInChart)
                    .WithOne(k => k.Ingredient)
                    .HasForeignKey(fk => fk.IngredientsInChartId);

        modelBuilder.Entity<IngredientsInChart>()
                    .HasOne(k => k.Ingredient)
                    .WithMany(c => c.IngredientsInChart)
                    .HasForeignKey(fk => fk.IngredientsInChartId);
        
    }
}

And finally, we could load the data as follow:

Eagerly Loading

Using eager load approach, we must load all the Ingredient before load the related IngredientsInChart:

[HttpGet]//return all ingredients in a chart by getting chartid 
public IHttpActionResult GetAlltheIngredientsInChart(int ChartId)
{
    var ingredients = db.Ingredients.ToList();

    var q = db.IngredientsInChart.Where(ingch => ingch.Id == ChartId)
                                 .Include(ingch => ingch.Ingredient)
                                 .Select(ingch => new { ingch.IngredientsInChartId, ingch.Ingredient.Name })
                                 .ToList();

    return Ok(q);
}

Lazy Loading

You don't need to load the ingredients (remove var ingredients = db.Ingredients.ToList(); and Include(ingch => ingch.Ingredient)). However, you might need to install the EF Proxy Nuget Package and setup in your Startup project as follow:

public void ConfigureServices(IServiceCollection services)
{
    #region Database configuration

    // Database configuration
    services.AddDbContext<MyData>(options =>
        options.UseLazyLoadingProxies()
               .UseSqlServer(Configuration.GetConnectionString("MyConnectionString")));

    #endregion Database configuration
}

For further reading, I strongly advise to read:

• Working with relationships in EF
• Querying related data
• Lazy Load with EF Proxies

Answer 2

provided you have navigation property Ingredient set in IngredientsInChart following query will work

var q = db.IngredientsInChart.Where(inc => inc.chartId == ChartId)
                             .Include(inc => inc.Ingredient)
                             .Select(inc => new { 
                                           inc.ingredientsInChartId,
                                           inc.Ingredient.name 
                                     }).ToList();