title: SQL to Linq then to Lambda using navigation properties
body: <p>I'm trying to build a lambda expression to get the grand total but I'm still struggling to achieve the desired result. I've managed to achieve the same using SQL and LINQ using joints but it would be great if someone could give me a hand to re-write the query using lambda and navigation properties (without joints).</p>
  <p>SQL Query:</p>
  <pre><code>SELECT SUM(a.[Quantity] * (a.[Price] + b.[ExtraValue])) + SUM(d.SubMealTotal *  a.[Quantity]) AS [Total]
    FROM [dbo].[OrderedMeals] a
    INNER JOIN [dbo].[OrderedMealPortions] b
    ON a.Id = b.[OrderedMealId]
    LEFT OUTER JOIN (SELECT OrderedMealId, Sum(Price) AS SubMealTotal FROM [dbo].[OrderedSubMeals]
          GROUP BY OrderedMealId) AS d
    ON a.Id = d.[OrderedMealId]
    WHERE a.[Quantity] &gt; 0
  </code></pre>
  <p>Then the LINQ - Please let me know if I'm missing something here or there is a better way:</p>
  <pre><code>(from orderedMeal in _context.OrderedMeals.Where(x =&gt; x.Quantity &gt; 0)
                          join orderedMealPortion in _context.OrderedMealPortions
                              on orderedMeal.Id equals orderedMealPortion.OrderedMealId
                          join orderedSubMeal in _context.OrderedSubMeals
                              on orderedMeal.Id equals orderedSubMeal.OrderedMealId into gs
                          from subOrderedSubMeal in gs.DefaultIfEmpty()
                          group subOrderedSubMeal by new { subOrderedSubMeal.OrderedMealId, orderedMeal.Price, orderedMeal.Quantity, orderedMealPortion.ExtraValue } into g
                          select new
                          {
                              MealTotal = (g.Key.ExtraValue + g.Key.Price) * g.Key.Quantity + g.Sum(x =&gt; x.Price * g.Key.Quantity),
                          }).Sum(x =&gt; x.MealTotal); // Not sure how to get the sum using LINQ
  </code></pre>
  <p>Entities:</p>
  <pre><code>public class OrderedMeal
  {
      public int Id { get; set; }
      public int Quantity { get; set; }
      public decimal Price { get; set; }
      public int OrderedMealPortionId { get; set; }
      public OrderedMealPortion? OrderedMealPortion { get; set; }
      public ICollection&lt;OrderedSubMeal&gt;? OrderedSubMeals { get; set; }
  
  }
  
  public class OrderedMealPortion
  {
      public int Id { get; set; }
      public int OrderedMealId { get; set; }
      public OrderedMeal? OrderedMeal { get; set; }
      public decimal? ExtraValue { get; set; }
  }
  
  public class OrderedSubMeal
  {
      public int Id { get; set; }
      public int OrderedMealId { get; set; }
      public OrderedMeal? OrderedMeal { get; set; }
      public decimal Price { get; set; }
  }
  </code></pre>