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);
};
}
