Using LINQ to SQL Template – Part Two

// Join example
var query = from c in db.Customers
            join o in db.Orders on c.CustomerID equals o.CustomerID
            select new { c.Name, o.OrderDate, o.Total };

// Group by example
var grouped = from o in db.Orders
              group o by o.CustomerID into g
              select new { CustomerID = g.Key, TotalOrders = g.Count() };

// Aggregate functions
var stats = from o in db.Orders
            group o by o.CustomerID into g
            select new {
                CustomerID = g.Key,
                TotalAmount = g.Sum(x => x.Total),
                AverageAmount = g.Average(x => x.Total),
                MaxOrder = g.Max(x => x.Total)
            };

// Insert
Customer newCustomer = new Customer { Name = "John", Email = "[email protected]" };
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();

// Update
var customer = db.Customers.First(c => c.CustomerID == 1);
customer.Name = "Updated Name";
db.SubmitChanges();

// Delete
var toDelete = db.Customers.First(c => c.CustomerID == 1);
db.Customers.DeleteOnSubmit(toDelete);
db.SubmitChanges();