public void ChangeProduct() { using (var connection = new DbNorthwind()) { var orders = from o in connection.Orders where o.ShippedDate == null select o; var order = orders.FirstOrDefault(); var result = orders.ToList(); var notShippedOrders = from od in connection.OrderDetails where od.OrderID == result.Select(x => x.OrderID).FirstOrDefault() select od; var notShippedOrder = notShippedOrders.FirstOrDefault(); var resultNotShippedOrders = notShippedOrders.ToList(); var forReplacement = notShippedOrders.ToList(); foreach (var item in forReplacement) { item.ProductID = 22; } connection.OrderDetails .Where(p => p.OrderID == notShippedOrders.Select(x => x.OrderID).FirstOrDefault()) .Delete(); foreach (var unit in forReplacement) { connection.OrderDetails.Value(p => p.OrderID, unit.OrderID) .Value(p => p.ProductID, unit.ProductID) .Value(p => p.Quantity, unit.Quantity).Insert(); } }; }
public void MoveProductToAnotherCategory() { using (var connection = new DbNorthwind()) { connection.Products .Where(p => p.ProductID == 10) .Set(p => p.CategoryID, 2) .Update(); }; }
public void AddEmployeeWithTerritory() { using (var connection = new DbNorthwind()) { connection.Employees .Value(p => p.FirstName, "First Name") .Value(p => p.LastName, "Last Name") .Value(p => p.Title, "Representative") .Value(p => p.Region, "WA").InsertWithInt32Identity(); }; }
public void GetRegionsAndEmployees() { using (var connection = new DbNorthwind()) { var query = from e in connection.Employees group e by e.City into g select new { City = g.Key, Count = g.Count() }; foreach (var employee in query) { Console.WriteLine($"City: {employee.City}, Number of Employees: {employee.Count}"); } }; }
public void GetEmployeesWithRegion() { using (var connection = new DbNorthwind()) { var query = from e in connection.Employees where e.Region == "WA" select e; foreach (var employee in query) { Console.WriteLine($"EmployeeId: {employee.EmployeeID}, Employee First Name: {employee.FirstName}, Employee Last Name: {employee.LastName} " + $"Employee Title: {employee.Title}, Employee Region: {employee.Region}"); } }; }
public void GetProductsWithCategoryAndSupplier() { using (var connection = new DbNorthwind()) { var query = from p in connection.Products from c in connection.Categories from s in connection.Suppliers where p.CategoryID == c.CategoryID && p.SupplierID == s.SupplierID select new { Product = p.ProductName, Category = c.CategoryName, Supplier = s.CompanyName }; foreach (var product in query) { Console.WriteLine($"Product: {product.Product}, Category: {product.Category}, Supplier: {product.Supplier}"); } }; }
public void GetEmployeesAndShippers() { using (var connection = new DbNorthwind()) { var query = from e in connection.Employees from o in connection.Orders from s in connection.Shippers where o.EmployeeID == e.EmployeeID && o.ShipVia == s.ShipperID select new { Employee = e.FirstName, Order = o.OrderID, Shipper = s.CompanyName }; foreach (var employee in query) { Console.WriteLine($"Employee: {employee.Employee} with order No: {employee.Order} worked with Shipper: {employee.Shipper}"); } }; }
public void AddListOfProductsWithSupplierAndCategory() { var list = new List <Product>() { new Product() { ProductName = "Product Name1", CategoryID = 3, SupplierID = 15 }, new Product() { ProductName = "Product Name2", CategoryID = 35, SupplierID = 18 }, new Product() { ProductName = "Product Name3", CategoryID = 11, SupplierID = 33 } }; using (var connection = new DbNorthwind()) { connection.BulkCopy(list); }; }