Problem with LINQ using the values of a table before a GROUP BY

I have the following GROUP BY:

   var stocks =
       from p in products
       from w in p.Warehouses
       from l in w.locations
       group l by l.Date into g
       let maxDate = g.Max(l1 => l1.Date)
       select new { Product=p.Name, Location= g.Where(l2 => l2.Date == maxDate) };

But it isn't working, i think because I am doing a group by I am not allowed to use values from the tables before the group by in my select.

Here is the hierarchy of the objects.

 Products

   Each product has multiple warehouses

      Each warehouse has multiple locations.

I need to return all products and each product must contain the location name.

What is the criteria to find the location when there are multiple warehouses and multiple locations.

I must search in each warehouse and in turn each location... and return OUT OF ALL OF THEM the latest location (ONLY 1) which I detect using the Date == maxDate.

But it won't let me select the "Name" that was in p. It's not 开发者_StackOverflowin g as g is group by of "locations".

I think you want something like this:

var query = from product in products
            let location = (from warehouse in p.Warehouses
                            from location in p.Locations
                            orderby location.Date descending
                            select location).FirstOrDefault()
            select new { Product = product.Name,
                         Location = location };

Note that Location may be null, if there are no matching locations.

There's no need to use a let clause here, but I figured this would be the most readable way of writing the query.

Now you don't really need to sort all the locations, of course... but there's no MaxBy in LINQ, which is what you really want. If you're using LINQ to Objects you could write your own MaxBy method (and there's one in MoreLINQ); in LINQ to SQL etc I'd expect the database to optimize the query anyway.