static void ImportUsersFromXML(string filename, ProductShopContext context) { XmlReader reader = XmlReader.Create(filename); while (reader.Read()) { if (reader.Name == "user") { User user = new User(); if (reader.GetAttribute("first-name") != null) { user.FirstName = reader.GetAttribute("first-name"); } if (reader.GetAttribute("last-name") != null) { user.LastName = reader.GetAttribute("last-name"); } if (reader.GetAttribute("age") != null) { user.Age = int.Parse(reader.GetAttribute("age")); } context.Users.Add(user); context.SaveChanges(); } } }
static void ImportCategoriesFromJSON(string filename, ProductShopContext context) { StreamReader reader = new StreamReader(filename); List<Category> categories = JsonConvert.DeserializeObject<List<Category>>(reader.ReadToEnd()); reader.Close(); foreach (var category in categories) { context.Categories.Add(category); } context.SaveChanges(); }
private static void ExportCategoriesWithProductsCountToJSON(string filename, ProductShopContext context) { var categories = context.Categories .OrderByDescending(c => c.Products.Count) .Select(c => new { Category = c.Name, ProductsCount = c.Products.Count, AveragePrice = c.Products.Average(p => (decimal?)p.Price), TotalRevenue = c.Products.Sum(p => (decimal?)p.Price) }).ToList(); var json = JsonConvert.SerializeObject(categories, Formatting.Indented); WriteJSONToFile(filename, json); }
static void ExportProductsInRangeToJSON(decimal start, decimal end, string filename, ProductShopContext context) { var products = context.Products .Where(p => p.Price >= start && p.Price <= end && p.Buyer == null) .Include(p => p.Seller.FirstName) .Include(p => p.Seller.LastName) .Select(p => new { Name = p.Name, Price = p.Price, FullName = p.Seller.FirstName + " " + p.Seller.LastName }).ToList(); var json = JsonConvert.SerializeObject(products, Formatting.Indented); WriteJSONToFile(filename, json); }
static void ImportProductsFromJSON(string filename, ProductShopContext context) { StreamReader reader = new StreamReader(filename); List<Product> products = JsonConvert.DeserializeObject<List<Product>>(reader.ReadToEnd()); reader.Close(); Random randGenerator = new Random(); foreach (var product in products) { if ( !(randGenerator.Next(1, 100) < 30) ) { product.Buyer = context.Users.Find(randGenerator.Next(1, context.Users.Count())); } product.Seller = context.Users.Find(randGenerator.Next(1, context.Users.Count())); var category = context.Categories.Find(randGenerator.Next(1, context.Categories.Count())); product.Categories.Add(category); context.Products.Add(product); } context.SaveChanges(); }
private static void ExportSuccessfullySoldProductsToJSON(string filename, ProductShopContext context) { var users = context.Users .Where(u => u.Products.Any(p => p.Buyer != null)) .Include(u => u.Products) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, SoldProducts = u.Products .Where(p => p.Buyer != null) .Select(p => new { Name = p.Name, Price = p.Price, BuyerFirstName = p.Buyer.FirstName, BuyerLastName = p.Buyer.LastName }) }).ToList(); var json = JsonConvert.SerializeObject(users, Formatting.Indented); WriteJSONToFile(filename, json); }
static void Main() { Database.SetInitializer(new DropCreateDatabaseAlways<ProductShopContext>()); ProductShopContext context = new ProductShopContext(); ImportUsersFromXML("../../users.xml", context); ImportCategoriesFromJSON("../../categories.json", context); ImportProductsFromJSON("../../products.json", context); }
private static void ExportUsersAndSoldProductsToXML(string filename, ProductShopContext context) { var users = context.Users .Where(u => u.Products.Any(p => p.Buyer != null)) .Include(u => u.Products) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age, SoldProducts = u.Products .Where(p => p.Buyer != null) .Select(p => new { Name = p.Name, Price = p.Price }) }).ToList(); var settings = new XmlWriterSettings(); settings.ConformanceLevel = ConformanceLevel.Document; settings.Encoding = Encoding.UTF8; settings.OmitXmlDeclaration = false; settings.Indent = true; XmlWriter writer = XmlWriter.Create(filename, settings); writer.WriteStartDocument(); writer.WriteStartElement("users"); writer.WriteAttributeString("count", users.Count.ToString()); foreach (var user in users) { writer.WriteStartElement("user"); if (user.FirstName != null) { writer.WriteAttributeString("first-name", user.FirstName); } if (user.LastName != null) { writer.WriteAttributeString("last-name", user.LastName); } if (user.Age != null) { writer.WriteAttributeString("age", user.Age.ToString()); } writer.WriteStartElement("sold-products"); writer.WriteAttributeString("count", user.SoldProducts.Count().ToString()); foreach (var soldProduct in user.SoldProducts) { writer.WriteStartElement("product"); if (soldProduct.Name != null) { writer.WriteAttributeString("name", soldProduct.Name); } writer.WriteAttributeString("price", soldProduct.Price.ToString()); // product writer.WriteEndElement(); } // sold-products writer.WriteEndElement(); // user writer.WriteEndElement(); } // users writer.WriteEndElement(); writer.WriteEndDocument(); writer.Close(); }
static void Main() { /* * Query 1 - Products In Range * * Get all products in a specified price range (e.g. 500 to 1000) which have no buyer. * Order them by price (from lowest to highest). * Select only the product name, price and the full name of the seller. * */ ProductShopContext context = new ProductShopContext(); ExportProductsInRangeToJSON(500, 1000, "../../01.products-in-range.json", context); /* * Query 2 - Successfully Sold Products * * Get all users who have at least 1 sold item with a buyer. * Order them by last name, then by first name. * Select the person's first and last name. * For each of the sold products (products with buyers), * select the product's name, price and the buyer's first and last name. * Export the result to JSON. * */ ExportSuccessfullySoldProductsToJSON("../../02.successfully-sold-products.json", context); /* * Query 3 - Categories By Products Count * * Get all categories. * Order them by the number of products in that category (a product can be in many categories). * For each category select its name, the number of products, the average price of those products * and the total revenue (total price sum) of those products (regardless if they have a buyer or not). * */ ExportCategoriesWithProductsCountToJSON("../../03.categories-with-products-count.json", context); /* * Query 4 - Users and Products * * Get all users who have at least 1 sold product. * Order them by the number of sold products (from highest to lowest), * then by last name (ascending). * Select only their first and last name, age and for each product - name and price. * Export the results to XML. * */ ExportUsersAndSoldProductsToXML("../../04.users-and-products.xml", context); }