private static void ImportCarsWithParts(CarDealershipContext context) { Console.WriteLine("Importing cars.."); string jsnoFile = File.ReadAllText(@"..\..\Import\cars.json"); List <Car> cars = JsonConvert.DeserializeObject <List <Car> >(jsnoFile); int partCap = context.Parts.Count() + 1; foreach (var car in cars) { Random rnd1 = new Random(); System.Threading.Thread.Sleep(15); for (int i = 0; i < rnd1.Next(10, 20); i++) { // 10-20 parts Random rnd2 = new Random(); System.Threading.Thread.Sleep(15); int partId = rnd2.Next(1, partCap); var part = context.Parts.FirstOrDefault(p => p.Id == partId); car.Parts.Add(part); } Console.WriteLine($"Adding car: {car.Model}, {car.Make} with parts.."); context.Cars.Add(car); context.SaveChanges(); } }
private static void ImportSales(CarDealershipContext context) { Console.WriteLine("Importing sales.."); var carsCount = context.Cars.Count(); var customersCount = context.Customers.Count(); List <Sale> sales = new List <Sale>(); for (int i = 0; i < carsCount - 30; i++) { Random rnd1 = new Random(); System.Threading.Thread.Sleep(8); int carId = rnd1.Next(1, carsCount); Random rnd2 = new Random(); System.Threading.Thread.Sleep(8); int customerId = rnd1.Next(1, customersCount); int discount = GenerateDiscount(i, carId, customerId); Sale sale = new Sale { CarId = carId, CustomerId = customerId, DiscountPercentage = discount }; sales.Add(sale); } context.Sales.AddRange(sales); context.SaveChanges(); }
private static void ImportSuppliers(CarDealershipContext context) { Console.WriteLine("Importing suppliers.."); string jsnoFile = File.ReadAllText(@"..\..\Import\suppliers.json"); List <Supplier> suppliers = JsonConvert.DeserializeObject <List <Supplier> >(jsnoFile); context.Suppliers.AddRange(suppliers); context.SaveChanges(); }
private static void Query_3_LocalSuppliers(CarDealershipContext context) { var localSuppliers = context.Suppliers.Where(s => s.IsImporter == false).Select(x => new { x.Id, x.Name, PartsCount = x.Parts.Count }); PrintJson(localSuppliers); }
private static void Query_5_TotalSalesByCustomer(CarDealershipContext context) { var totalSalesByCustomer = context.Customers.Where(c => c.Sales.Count >= 1).Select(s => new { fullName = s.Name, boughtCars = s.Sales.Count, spentMoney = s.Sales.Select(x => x.Car.Parts.Sum(p => p.Price)).Sum() }).OrderByDescending(x => x.spentMoney); PrintJson(totalSalesByCustomer); }
private static void Query_6_SalesWithAppliedDiscount(CarDealershipContext context) { var salesWithAppliedDiscount = context.Sales.Select(s => new { car = new { s.Car.Make, s.Car.Model, s.Car.TravelledDistance }, customerName = s.Customer.Name, Discount = s.DiscountPercentage + (s.Customer.IsYoungDriver ? 5 : 0) }); PrintJson(salesWithAppliedDiscount); }
private static void Query_4_CarsWithTheirListOfParts(CarDealershipContext context) { var carsWithTheirListOfParts = context.Cars.Where(c => c.Id == 100).Select(c => new { Car = new { c.Make, c.Model, c.TravelledDistance, }, Parts = c.Parts.Select(p => new { p.Name, p.Price }) }); PrintJson(carsWithTheirListOfParts); }
private static void Query_2_CarsFromMakeToyota(CarDealershipContext context) { var carsFromMakeToyota = context.Cars.Where(c => c.Make == "Toyota") .OrderBy(c => c.Model) .ThenByDescending(c => c.TravelledDistance).Select(x => new { x.Id, x.Make, x.Model, x.TravelledDistance }); PrintJson(carsFromMakeToyota); }
private static void ImportParts(CarDealershipContext context) { Console.WriteLine("Importing parts.."); string jsnoFile = File.ReadAllText(@"..\..\Import\parts.json"); List <Part> parts = JsonConvert.DeserializeObject <List <Part> >(jsnoFile); var suppliersCount = context.Suppliers.Count(); foreach (var part in parts) { Random rnd1 = new Random(); System.Threading.Thread.Sleep(15); //Console.WriteLine(rnd1.Next(1, suppliersCount)); part.SupplierId = rnd1.Next(1, suppliersCount); } context.Parts.AddRange(parts); context.SaveChanges(); }
static void Main(string[] args) { using (var context = new CarDealershipContext()) { // 4. Car Dealer check DB Models here: .\Models\* context.Database.Initialize(true); // 5. Car Dealer Import Data ImportSuppliers(context); ImportParts(context); ImportCarsWithParts(context); ImportCustomers(context); ImportSales(context); // 6. Car Dealer Query and Export Data Query_1_OrderedCustomers(context); Query_2_CarsFromMakeToyota(context); Query_3_LocalSuppliers(context); Query_4_CarsWithTheirListOfParts(context); Query_5_TotalSalesByCustomer(context); Query_6_SalesWithAppliedDiscount(context); } }
private static void Query_1_OrderedCustomers(CarDealershipContext context) { var orderedCustomers = context.Customers.OrderBy(c => c.BirthDate).ThenBy(c => c.IsYoungDriver); PrintJson(orderedCustomers); }