How to Filter Results in LINQ to Objects Using Group Join?

Question

I have two lists: one representing stores and another representing orders associated with those stores. Here's a simplified version of the data:

var stores = new[] 
{
    new { Code = 1, Name = "Store 1" },
    new { Code = 2, Name = "Store 2" }    
};

var orders = new[] 
{
    new { Code = 1, StoreCode = 1, TotalValue = 4.12 },
    new { Code = 2, StoreCode = 1, TotalValue = 14.12 },
    new { Code = 3, StoreCode = 1, TotalValue = 24.12 }
};

I'm trying to achieve the following output: StoreName = Store 1 | TotalValue = 38.24

To accomplish this, I'm using a group join in LINQ to Objects to aggregate the orders for each store. However, I also need to filter the results based on a condition applied to the orders.

Here's the query I'm using:

var result = (from s in stores
              join o in orders on s.Code equals o.StoreCode into grp
              where grp.Any(o => o.TotalValue > 10)
              select new
              {
                  StoreName = s.Name,
                  TotalValue = grp.Sum(x => x.TotalValue)
              }).ToList();

However, I encountered an error: The name 'o' does not exist in the current context.

How can I filter the group joined data from the orders table effectively in this scenario?

Answer 1

Try this:

var result = (from s in stores
              join o in orders.Where(x => x.TotalValue > 10) on s.Code equals o.StoreCode into grp
              select new
              {
                  StoreName = s.Name,
                  TotalValue = grp.Sum(x => x.TotalValue)
              });

Or this

var result = (from s in stores
              join o in orders on s.Code equals o.StoreCode into grp
              select new
              {
                  StoreName = s.Name,
                  TotalValue = grp.Where(x => x.TotalValue > 10)
                                  .Sum(x => x.TotalValue)
              });

Note that it's usually not necessary to call ToList, as this requires immediate evaluation of the query. Most of the time, leaving it as IEnumerable<T> is sufficient. Also, this will also return a row for 'Store 2', with TotalValue = 0. If you'd like to omit these rows as well, you can use something like this.

var result = (from s in stores
              join o in orders.Where(x => x.TotalValue > 10) on s.Code equals o.StoreCode into grp
              let totalValue = grp.Sum(x => x.TotalValue)
              where totalValue > 0
              select new
              {
                  StoreName = s.Name,
                  TotalValue = totalValue 
              });

Or this

var result = (from s in stores
              join o in orders on s.Code equals o.StoreCode into grp
              let totalValue = grp.Where(x => x.TotalValue > 10)
                                  .Sum(x => x.TotalValue)
              where totalValue > 0
              select new
              {
                  StoreName = s.Name,
                  TotalValue = totalValue 
              });