SQL to Linq then to Lambda using navigation properties-CodePudding

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).

SQL Query:

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] > 0

Then the LINQ - Please let me know if I'm missing something here or there is a better way:

(from orderedMeal in _context.OrderedMeals.Where(x => x.Quantity > 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 => x.Price * g.Key.Quantity),
                        }).Sum(x => x.MealTotal); // Not sure how to get the sum using LINQ

Entities:

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<OrderedSubMeal>? 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; }
}

CodePudding user response：

I can't test with a database, but I think this implements the query logic and will produce the same result:

var ans = OrderedMeals
          .Where(om => om.Quantity > 0 && om.OrderedMealPortion != null)
          .Sum(om => om.Quantity * (om.Price   
                                    om.OrderedMealPortion!.ExtraValue   
                                    (om.OrderedSubMeals != null ? om.OrderedSubMeals.Sum(osm => osm.Price) : 0)) );

CodePudding user response：

This is direct translation from the SQL:

var groupingQuery = 
    from sm in  _context.OrderedSubMeals
    group sm by new { sm.OrderedMealId } into g
    select new 
    {
        g.Key.OrderedMealId,
        SubMealTotal = g.Sum(x => x.Price)
    };

var query = 
    from om in _context.OrderedMeals
    join g in groupingQuery on om.Id equals g.OrderedMealId into gj
    from g in gj.DefaultIfEmpty()
    where om.Quentity > 0
    select new { om, om.OrderedMealPortion, g };

var result = query.Sum(x => x.om.Quantity * (x.om.Price   x.OrderedMealPortion.ExtraValue   x.g.SubMealTotal));

But I have feeling that query can be simplified without grouping.