private static void Having() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var groups = db.Products .GroupBy(p => p.CategoryId) .Select(g => new { g.Key, CountProduct = g.Count(), SumQuantities = g.Sum(p => p.Quantity), MinPrice = g.Min(p => p.Price), MaxPrice = g.Max(p => p.Price), AvgPrice = g.Average(p => p.Price) }).Where(g => g.SumQuantities > 20) .ToList(); foreach (var group in groups) { Console.WriteLine("Category Id: " + group.Key); Console.WriteLine("Count Product: " + group.CountProduct); Console.WriteLine("Sum Quantities : " + group.SumQuantities); Console.WriteLine("Min Price: " + group.MinPrice); Console.WriteLine("Max Price: " + group.MaxPrice); Console.WriteLine("Avg Price: " + group.AvgPrice); } Console.ReadLine(); }
private static void LimitIn() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); Console.WriteLine("Find 2 products with the biggest price"); var productList1 = db.Products.OrderByDescending(p => p.Price).Skip(0).Take(2).ToList(); foreach (var product in productList1) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.WriteLine("\nConditions in Limit"); var productList3 = db.Products.Where(p => p.CategoryId == 1).OrderByDescending(p => p.Id).Skip(0).Take(2).ToList(); foreach (var product in productList3) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void Avg() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var avgPrice = db.Products.Average(p => p.Price); Console.WriteLine("Average price of the products: " + avgPrice); Console.ReadLine(); }
private static void Delete() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var product = db.Products.SingleOrDefault(p => p.Id == 6); db.Products.Remove(product); Console.WriteLine("Result: " + db.SaveChanges()); Console.ReadLine(); }
private static void Update() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var product = db.Products.SingleOrDefault(p => p.Id == 6); product.Name = "abc"; product.Price = 999; product.Status = false; Console.WriteLine("Result: " + db.SaveChanges()); Console.ReadLine(); }
private static void DateTime() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); Console.WriteLine("Use Year, Month and Day in DateTime"); var productsList1 = db.Products.Where(p => p.CreationDate.Value.Year == 2016 && p.CreationDate.Value.Month == 11 && p.CreationDate.Value.Day == 20).ToList(); foreach (var product in productsList1) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.WriteLine("\nCompare Date"); var productsList2 = db.Products.Where(p => p.CreationDate.Value >= new System.DateTime(2017, 12, 15) && p.CreationDate.Value <= new DateTime(2017, 12, 25)).ToList(); foreach (var product in productsList2) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.WriteLine("\nSubstract Date"); var passedDate = System.DateTime.Now.AddDays(-3); Console.WriteLine(passedDate.ToShortDateString()); var productsList3 = db.Products.Where(p => DbFunctions.TruncateTime(p.CreationDate.Value) == DbFunctions.TruncateTime(passedDate)).ToList(); foreach (var product in productsList3) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void MinAndMax() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var max = db.Products.Max(p => p.Price); Console.WriteLine("The biggest price: " + max); var min = db.Products.Min(p => p.Price); Console.WriteLine("The smallest price: " + min); Console.ReadLine(); }
private static void Count() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var count1 = db.Products.Count(); Console.WriteLine("Count Products: " + count1); var count2 = db.Products.Count(p => p.Status == true); Console.WriteLine("Conditions in Count Products: " + count2); Console.ReadLine(); }
private static void Sum() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var sum1 = db.Products.Sum(p => p.Quantity); Console.WriteLine("Sum Quantities: " + sum1); var sum2 = db.Products.Where(p => p.Status == true).Sum(p => p.Quantity); Console.WriteLine("Conditions in Sum Quantities: " + sum2); var sum3 = db.Products.Sum(p => p.Price * p.Quantity); Console.WriteLine("Total: " + sum3); Console.ReadLine(); }
private static void SortIn() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var products = db.Products.OrderBy(p => p.Price).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.WriteLine("\nSort By Price Descending"); var productList2 = db.Products.OrderByDescending(p => p.Price).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.WriteLine("\nConditions with sort by"); var productList3 = db.Products.Where(p => p.Status == true).OrderByDescending(p => p.Price).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void UseLessThanOrEqualOperator() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var products = db.Products.Where(p => p.Price <= 15).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void Insert() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var product = new Product() { Name = "Tivi 3", Price = 12, Quantity = 3, CreationDate = System.DateTime.Now, Status = true, CategoryId = 3 }; db.Products.Add(product); Console.WriteLine("Result: " + db.SaveChanges()); Console.ReadLine(); }
private static void GetEntitiesList() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var products = db.Products.ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void LikeClause() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var products = db.Products.Where(p => p.Name.StartsWith("mob")).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } Console.ReadLine(); }
private static void Projection() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var products = db.Products.Select(p => new { Id = p.Id, Name = p.Name, Price = p.Price, Quantity = p.Quantity, Total = p.Price.Value * p.Quantity.Value }).ToList(); foreach (var product in products) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Total); } Console.ReadLine(); }
private static void FindEntityByPrimaryKey() { LearnEntityFrameworkDB db = new LearnEntityFrameworkDB(); var product = db.Products.SingleOrDefault(p => p.Id == 2); if (product != null) { Console.WriteLine("Id: " + product.Id); Console.WriteLine("Name: " + product.Name); Console.WriteLine("Price : " + product.Price); Console.WriteLine("Quantity: " + product.Quantity); Console.WriteLine("Status: " + product.Status); Console.WriteLine("Creation Date: " + product.CreationDate.Value.ToShortDateString()); Console.WriteLine("Category Id: " + product.Category.Id); Console.WriteLine("Category Name: " + product.Category.Name); } else { Console.WriteLine("Product does not exist"); } Console.ReadLine(); }