static void TestTables() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { // retrieve all Beverages products IEnumerable<Product> beverages = from p in NWEntities.Products where p.Category.CategoryName == "Beverages" orderby p.ProductName select p; Console.WriteLine("There are {0} Beverages", beverages.Count()); // update one product Product bev1 = beverages.ElementAtOrDefault(10); if (bev1 != null) { decimal newPrice = (decimal)bev1.UnitPrice + 10.00m; Console.WriteLine("The price of {0} is {1}. Update to {2}", bev1.ProductName, bev1.UnitPrice, newPrice); bev1.UnitPrice = newPrice; } // submit the change to database NWEntities.SaveChanges(); // insert a product Product newProduct = new Product { ProductName = "new test product" }; NWEntities.Products.Add(newProduct); NWEntities.SaveChanges(); Console.WriteLine("Added a new product"); // delete a product IQueryable<Product> productsToDelete = from p in NWEntities.Products where p.ProductName == "new test product" select p; if (productsToDelete.Count() > 0) { foreach (var p in productsToDelete) { NWEntities.Products.Remove(p); Console.WriteLine("Deleted product {0}", p.ProductID); } NWEntities.SaveChanges(); } } }
static void TestExplicitTransaction() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { using (TransactionScope ts = new TransactionScope()) { try { Product prod1 = (from p in NWEntities.Products where p.ProductID == 4 select p).First(); prod1.UnitPrice += 1; NWEntities.SaveChanges(); Console.WriteLine("First update saved to database, but not commited."); // now let's try to update another product Product prod2 = (from p in NWEntities.Products where p.ProductID == 5 select p).First(); // update will fail because UnitPrice can't be < 0 prod2.UnitPrice = -5; NWEntities.SaveChanges(); ts.Complete(); } catch (Exception e) { // both updates will fail because they are within one transaction Console.WriteLine("Exception caught. Rollback the first update."); } } } }
static void TestVersionControl() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { // first user Console.WriteLine("First User ..."); Product product = (from p in NWEntities.Products where p.ProductID == 3 select p).First(); Console.WriteLine("Original unit in stock: {0}", product.UnitsInStock); product.UnitsInStock += 1; Console.WriteLine("Current unit in stock to update: {0}", product.UnitsInStock); // process more products // second user Console.WriteLine("\nSecond User ..."); using (NorthwindEntities1 NWEntities1 = new NorthwindEntities1()) { Product1 product1 = (from p in NWEntities1.Product1s where p.ProductID == 3 select p).First(); Console.WriteLine("Original unit in stock: {0}", product1.UnitsInStock); product1.UnitsInStock += 2; Console.WriteLine("Current unit in stock to update: {0}", product1.UnitsInStock); NWEntities1.SaveChanges(); Console.WriteLine("update submitted to database"); } // first user is ready to submit changes Console.WriteLine("\nFirst User ..."); try { NWEntities.SaveChanges(); } catch (DbUpdateConcurrencyException e) { Console.WriteLine("Conflicts detected. Refreshing ..."); var entry = e.Entries.Single(); entry.OriginalValues.SetValues(entry.GetDatabaseValues()); NWEntities.SaveChanges(); Console.WriteLine("update submitted to database after refresh"); } } }
static void TestImplicitTransaction() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { Product prod1 = (from p in NWEntities.Products where p.ProductID == 4 select p).First(); Product prod2 = (from p in NWEntities.Products where p.ProductID == 5 select p).First(); prod1.UnitPrice += 1; // update will be saved to database NWEntities.SaveChanges(); Console.WriteLine("First update saved to database"); prod2.UnitPrice = -5; // update will fail because UnitPrice can't be < 0 // but previous update stays in database try { NWEntities.SaveChanges(); Console.WriteLine("Second update saved to database"); } catch (Exception) { Console.WriteLine("Second update not saved to database"); } } }
static void TestTPHInheritance() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { var USACustomers1 = from c in NWEntities.BaseCustomers where c is USACustomer select c; var USACustomers2 = from c in NWEntities.BaseCustomers.OfType<USACustomer>() select c; Console.WriteLine("Total number of USA customers: {0}", USACustomers1.Count()); Console.WriteLine("Total number of USA customers: {0}", USACustomers2.Count()); var USACustomers3 = from c in NWEntities.BaseCustomers select c as USACustomer; foreach (var c in USACustomers3) { if (c != null) { Console.WriteLine("USA customer: {0}, Phone: {1}", c.CompanyName, c.Phone); } } var UKCustomers = from c in NWEntities.BaseCustomers.OfType<UKCustomer>() select c; foreach (var c in UKCustomers) Console.WriteLine("UK customer: {0}, Fax: {1}", c.CompanyName, c.Fax); } }
static void TestTPTInheritance() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { var usaCustomer1 = (from c in NWEntities.BaseCustomers.OfType<USACustomer1>() select c).FirstOrDefault(); if (usaCustomer1 != null) { var phone1 = usaCustomer1.Phone; Console.WriteLine("Phone for USA customer1:{0}", phone1); } var ukCustomer1 = (from c in NWEntities.BaseCustomers.OfType<UKCustomer1>() select c).FirstOrDefault(); if (ukCustomer1 != null) { var fax1 = ukCustomer1.Fax; Console.WriteLine("Fax for UK customer1:{0}", fax1); } //var usaCustomerx = (from c // in NWEntities.BaseCustomers.OfType<USACustomer>() // where c.CustomerID == usaCustomer1.CustomerID // select c // ).SingleOrDefault(); var usaCustomer = (from c in NWEntities.BaseCustomers.OfType<USACustomer>() where c.CustomerID == usaCustomer1.CustomerID select new { CustomerID = "new PK", c.Phone } ).SingleOrDefault(); if (usaCustomer != null) { var phone = usaCustomer.Phone; Console.WriteLine( "Phone for USA customer from Customers table:{0}", phone); } } }
static void TestView() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { var currentProducts = from p in NWEntities.Current_Product_Lists select p; foreach (var p in currentProducts) { Console.WriteLine("Product ID: {0} Product Name: {1}", p.ProductID, p.ProductName); } } }
static void TestStoredProcedure() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { IEnumerable<Ten_Most_Expensive_Products_Result> tenProducts = from p in NWEntities.Ten_Most_Expensive_Products() select p; Console.WriteLine("Ten Most Expensive Products:"); foreach (Ten_Most_Expensive_Products_Result p in tenProducts) { Console.WriteLine("Product Name: {0}, Price; {1}", p.TenMostExpensiveProducts, p.UnitPrice); } // map a stored procedure to an entity class Product getProduct = NWEntities.GetProduct(1).FirstOrDefault(); Console.WriteLine("\nProduct name for product 1:{0}", getProduct.ProductName); } }
static void TestJoin() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { var categoryProducts = from c in NWEntities.Categories join p in NWEntities.Products on c.CategoryID equals p.CategoryID into productsByCategory select new { c.CategoryName, productCount = productsByCategory.Count() }; foreach (var cp in categoryProducts) { Console.WriteLine("There are {0} products in category {1}", cp.productCount, cp.CategoryName); } } }
static void TestEagerLoading() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { // eager loading products of categories var categories = from c in NWEntities.Categories.Include(c => c.Products) select c; foreach (var category in categories) { Console.WriteLine("There are {0} products in category {1}", category.Products.Count(), category.CategoryName); } } }
static void TestAssociation() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { var categories = from c in NWEntities.Categories select c; foreach (var category in categories) { Console.WriteLine("There are {0} products in category {1}", category.Products.Count(), category.CategoryName); } } }
static void TestDeferredExecution() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { // SQL is not executed IQueryable<Product> beverages = from p in NWEntities.Products where p.Category.CategoryName == "Beverages" orderby p.ProductName select p; // SQL is executed on this statement Console.WriteLine("There are {0} Beverages", beverages.Count()); // SQL is executed on this statement decimal? averagePrice = (from p in NWEntities.Products select p.UnitPrice).Average(); Console.WriteLine("The average price is {0}", averagePrice); // SQL is not executed even there is a singleton method var cheapestProductsByCategory = from p in NWEntities.Products group p by p.CategoryID into g select new { CategoryID = g.Key, CheapestProduct = (from p2 in g where p2.UnitPrice == g.Min(p3 => p3.UnitPrice) select p2).FirstOrDefault() }; // SQL is executed on this statement Console.WriteLine("Cheapest products by category:"); foreach (var p in cheapestProductsByCategory) { if (p.CategoryID == null || p.CheapestProduct == null) continue; Console.WriteLine( "categery {0}: product name: {1} price: {2}", p.CategoryID, p.CheapestProduct.ProductName, p.CheapestProduct.UnitPrice); } } }
static void ViewGeneratedSQL() { using (NorthwindEntities NWEntities = new NorthwindEntities()) { IQueryable<Product> beverages = from p in NWEntities.Products where p.Category.CategoryName == "Beverages" orderby p.ProductName select p; // view SQL using ToString method Console.WriteLine("The SQL statement is:\n" + beverages.ToString()); } }