Using LINQ to Group and Aggregate with Complex Types in EF Core 6
I've been banging my head against this for hours. I'm trying to group a collection of entities using LINQ in EF Core 6, but I'm running into a question when aggregating over nested properties. I have a `Product` entity that contains a list of `Order` entities, and I want to group products by their category and calculate the total sales amount for each category. The structure looks like this: ```csharp public class Product { public int Id { get; set; } public string Name { get; set; } public string Category { get; set; } public ICollection<Order> Orders { get; set; } } public class Order { public int Id { get; set; } public decimal Amount { get; set; } public int ProductId { get; set; } } ``` When I write a LINQ query to group the products by category and sum the order amounts, it seems to perform the aggregation incorrectly. Here’s my current LINQ query: ```csharp var result = await _context.Products .Include(p => p.Orders) .GroupBy(p => p.Category) .Select(g => new { Category = g.Key, TotalSales = g.SelectMany(p => p.Orders).Sum(o => o.Amount) }).ToListAsync(); ``` However, I get `InvalidOperationException: The LINQ expression could not be translated.` It seems that the scenario arises from using `SelectMany` in the context of grouping. I've also tried restructuring the query but need to quite get it to work. I've already confirmed that the `Orders` collection is being loaded correctly and contains valid data. When I run a simple query to return products with their orders, everything displays as expected. Here’s the query I tried before: ```csharp var testQuery = await _context.Products .Include(p => p.Orders) .ToListAsync(); ``` This works fine. I'm also aware that I'm using EF Core 6 and have set up lazy loading properly. Does anyone have suggestions on how to correctly structure the LINQ query for grouping and aggregating nested collections? Any insights or examples would be greatly appreciated! My development environment is Ubuntu. Any help would be greatly appreciated! For context: I'm using Csharp on Ubuntu. Thanks in advance!