how would you query this in linq?

Question

lets say i have 2 tables: products (just product ID and name) and sales (sale ID, product ID, amount, date)

now, given a start date and end date, i want to sum for every product its total sales amount in the given time frame notice that naturally some products will just have zero sales

how should i write this query?

Answer 1

var products = 
    from p in mycontext.Products
    select new
    {
       Product = p,
       Sales = p.Sales
          .Where(s=>s.StartDate > startDate && s.EndDate < endDate)
          .Sum(s=>s.amount)
    }