LINQ to SQL How to join two tables, get the full class object from one but replace one field from the other

Question

I am working on a program that will convert recipes to healthier versions. It has regular ingredients and more healthy ingredients, and people input their recipes, and it makes suggestions of healthier substitutions.

There are three tables: Ingredient, Recipe, and IngredientList, which holds the list of ingredients and the amounts of each ingredient for each recipe. I am using LINQ to SQL. Here is the IngredientList.

[IngrListId]       INT        IDENTITY (1, 1) NOT NULL,
[RecipeId]         INT        NOT NULL,
[IngredientId]     INT        NOT NULL,
[IngredientAmount] FLOAT (53) NOT NULL,
 PRIMARY KEY CLUSTERED ([IngrListId] ASC),
 CONSTRAINT [FK_IngredientList_Recipe] FOREIGN KEY ([RecipeId]) REFERENCES [dbo].[Recipe] ([RecipeId]),
 CONSTRAINT [FK_IngredientList_Ingredient] FOREIGN KEY ([IngredientId]) REFERENCES [dbo].[Ingredient] ([IngredientId])

My issue is how to edit the Recipe after it has been saved. I can bring back all of the Recipe fields, and the list of Ingredients, but I don’t know how to bring back the ingredients and match them up with the correct amount. Or rather, I do know how, but if I do so in the way I know how, it is no longer a list of ingredients, but a list of a new item that are not ingredients, and I am no longer able to add new ingredients to that list.

I need to take just the ingredientLists.IngredientAmount value from the IngredientLists table, and replace the ingredients.Amount property with it for each ingredient I bring in, but keep the Ingredient object identifiable as an Ingredient.

So if I bring it in like this I get my list of ingredients. Since the amount of each ingredient is kept in a different field, that information is lost (it goes to the default of 1), but the datagrid works, and I can add and edit the ingredients normally with the already working code on the rest of the page.

var ingrlist = from ingredients in dbContext.Ingredients
      join ingredientLists in dbContext.IngredientLists on ingredients.IngredientId equals ingredientLists.IngredientId
      where ingredientLists.RecipeId == rid
      select ingredients;
dgvRegIngrList.DataSource = ingrlist;

If I use this code, I can pull the correct amounts in, but it is no longer a list of ingredients. If I now try to add a new ingredient to the list or edit one of the existing ones using my interface designed for ingredients, I get errors.

var ingrlist = from ingredients in dbContext.Ingredients
                               join ingredientLists in dbContext.IngredientLists on ingredients.IngredientId equals ingredientLists.IngredientId
                               where ingredientLists.RecipeId == rid
                               select new
                               {
                                   ingredients.IngredientId,
                                   ingredients.Name,
                                   ingredients.Units,
                                   ingredientLists.IngredientAmount,
                                   ingredients.Calories,
                                   ingredients.Fat,
                                   ingredients.Carbs,
                                   ingredients.Protein,
                                   ingredients.Discriminator
                               };

                dgvRegIngrList.DataSource = ingrlist;

This question has a picture of the interface and the full code for the page from a little while ago, if more context helps. Datagrid from list crashes when item selected

Answer 1

I figured it out. I get the IngredientList with the correct RecipeID, then do a foreach loop to get the right amount to each ingredient. Works perfectly. I can add and edit the ingredients because they are proper ingredients, but they have the correct amounts as they come in.

And yes, I am using LINQ to SQL. That is what I found a tutorial on, so that is what I started using. If something else is newer or better, I will try it on my next project, but I am pretty much committed to this for this one.

var ingrlist = from il in dbContext.IngredientLists where il.RecipeId == rid select il;

       foreach (var ing in ingrlist)
          {
            var ingr = (from r in dbContext.Ingredients where r.IngredientId == ing.IngredientId select r).First();
            ingr.Amount = ing.IngredientAmount;
            selectedIngredients.Add(ingr);
          }