CODEWITHSUNDEEP
c#postgresqlentity-frameworklinq
Samuel Mungy
Samuel Mungy

Reputation: 477

Group by Sum for Calculated field in Linq

I want to get create a calculated field for a three way join between tables. The three tables are named Recipes, Ingredients and IngredientsToRecipes. The values that these tables carry are as follows:

Recipes

Ingredients

IngredientsToRecipes

Now, I started off by doing a three-way join and then grouping by recipe since there would be many duplicates from the join, here is how I did that:

var recipesJoin = (
    from a in db.IngredientsToRecipes
    join b in db.Recipes on a.recipeID equals b.recipeID
    join c in db.Ingredients on a.siin equals c.siin
    select new
    {
        recipeID = a.recipeID,
        userID = b.userID,
        name = b.name,
        description = b.description,
        price = c.price
    }).GroupBy(x=>x.recipeID);

My plan was to then create a new table from recipesJoin whereby I would sum the price up and then only return the rows that have price below a variable y. I've tried many things but my understanding of Linq started today so I'm severely limited. I tried

var recipesJoin = (
    from a in db.IngredientsToRecipes
    join b in db.Recipes on a.recipeID equals b.recipeID
    join c in db.Ingredients on a.siin equals c.siin
    select new
    {
        recipeID = a.recipeID,
        userID = b.userID,
        name = b.name,
        description = b.description,
        price = c.price
    }).GroupBy(x=>x.recipeID).Sum(y=>y.price);

but I get the error:

Severity Code Description Project File Line Suppression State Error CS1061 'IGrouping>' does not contain a definition for 'price' and no extension method 'price' accepting a first argument of type 'IGrouping>' could be found (are you missing a using directive or an assembly reference?) SalWebAPI C:\Users\Samuel.endeva\source\repos\SalApp\SalApp\SalWebAPI\Controllers\RecipeTypeBudgetController.cs 31 Active

I don't quite understand the error. My main goal is to sum and group into a calculated field, remove the rows that are above a certain price and then join that new table with another to do a simple check. How do I do the sum for a 3 way join like this?

Upvotes: 0

Views: 602

Answers (1)

Oxald
Oxald

Reputation: 837

You should select the result after the grouping action. Since you are grouping by recipeID I believe you want the sum price for each unique recipe ID as a result here is the proposal:

var recipesJoin = (
      from a in db.IngredientsToRecipes
      join b in db.Recipes on a.recipeID equals b.recipeID
      join c in db.Ingredients on a.siin equals c.siin
      select new
      {
          recipeID = a.recipeID,
          userID = b.userID,
          name = b.name,
          description = b.description,
          price = c.price
      }).GroupBy(x => x.recipeID) // 1
          .Select(grp=> new //2
          {
              recipeID = grp.Key,
              name= grp.First().name, // same ID => same name anyway
              toalPrice = grp.Sum(b => b.price) //3
          })
          .Where(y => y.totalPrice < 2000); //4

1- group by recipedID

2- Select result to get different entities for each unique recipe ID

3- Here you can do the sum for each unique recipeID (got though y.Key==grouping key)

4- Filter the results (replace 2000 by your real threshold)

Upvotes: 2

Related Questions