6. Joins: inner, left, group join, cross

var customers = new[] { new Customer(1, "Acme"), new Customer(2, "Zen"), new Customer(3, "Nova") };
var orders = new[] { new Order(101, 1, 500m), new Order(102, 1, 250m), new Order(103, 2, 900m) };

// INNER JOIN
var inner = customers.Join(orders, c => c.Id, o => o.CustomerId, (c, o) => new { c.Name, o.Id, o.Total });

// query syntax
var inner2 = from c in customers
             join o in orders on c.Id equals o.CustomerId
             select new { c.Name, o.Total };

// LEFT JOIN — classic GroupJoin + SelectMany + DefaultIfEmpty
var left = from c in customers
           join o in orders on c.Id equals o.CustomerId into custOrders
           from o in custOrders.DefaultIfEmpty()
           select new { c.Name, OrderId = o?.Id, Total = o?.Total ?? 0 };

// .NET 10: built-in LeftJoin operator
var left10 = customers.LeftJoin(orders, c => c.Id, o => o.CustomerId, (c, o) => new { c.Name, Total = o?.Total ?? 0 });

// GROUP JOIN — each customer with its orders
var perCustomer = customers.GroupJoin(orders, c => c.Id, o => o.CustomerId,
    (c, os) => new { c.Name, Orders = os.Count(), Sum = os.Sum(o => o.Total) });

// CROSS JOIN
var cross = from size in new[] { "S", "M" } from color in new[] { "Red", "Blue" } select size + color;

record Customer(int Id, string Name);
record Order(int Id, int CustomerId, decimal Total);