LINQ group by查询帮助:选择另一个带键的相关表

// 假设有两个实体类：Order（订单）和Product（产品）
public class Order
{
    public int OrderId { get; set; }
    public string CustomerName { get; set; }
    public decimal TotalAmount { get; set; }
    public int ProductId { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
}

// 假设有一个订单列表和一个产品列表
List<Order> orders = new List<Order>
{
    new Order { OrderId = 1, CustomerName = "Alice", TotalAmount = 100, ProductId = 1 },
    new Order { OrderId = 2, CustomerName = "Bob", TotalAmount = 200, ProductId = 2 },
    new Order { OrderId = 3, CustomerName = "Alice", TotalAmount = 150, ProductId = 1 },
    new Order { OrderId = 4, CustomerName = "Bob", TotalAmount = 120, ProductId = 2 },
};

List<Product> products = new List<Product>
{
    new Product { ProductId = 1, ProductName = "Apple", Price = 10 },
    new Product { ProductId = 2, ProductName = "Banana", Price = 20 },
};

// 使用LINQ进行group by查询并选择另一个带键的相关表
var query = from order in orders
            join product in products on order.ProductId equals product.ProductId
            group order by order.CustomerName into g
            select new
            {
                CustomerName = g.Key,
                TotalAmount = g.Sum(order => order.TotalAmount),
                Products = from order in g
                           select new
                           {
                               ProductName = products.First(p => p.ProductId == order.ProductId).ProductName,
                               Quantity = 1
                           }
            };

// 输出查询结果
foreach (var result in query)
{
    Console.WriteLine($"Customer: {result.CustomerName}");
    Console.WriteLine($"Total Amount: {result.TotalAmount}");
    Console.WriteLine("Products:");
    foreach (var product in result.Products)
    {
        Console.WriteLine($"- {product.ProductName} x {product.Quantity}");
    }
    Console.WriteLine();
}