static void GenerateXmlWithData(string path, ShopEntities ctx) { var users = ctx.Users .Where(u => u.ProductsSold.Count() >= 1) .OrderByDescending(u => u.ProductsSold.Count()) .ThenBy(u => u.LastName) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age, SoldProducts = u.ProductsSold .Select(p => new { Name = p.Name, Price = p.Price }) }).ToList(); int usersCount = users.Count(); var doc = new XDocument(); var usersElement = new XElement("users"); XAttribute attribute = new XAttribute("count", usersCount); usersElement.Add(attribute); foreach(var user in users) { XElement xmlUser = new XElement("user"); if (user.FirstName != null) xmlUser.Add(new XAttribute("first-name", user.FirstName)); xmlUser.Add(new XAttribute("last-name", user.LastName)); if (user.Age != null) xmlUser.Add(new XAttribute("age", user.Age)); var soldProducts = new XElement("sold-products"); soldProducts.Add(new XAttribute("count", user.SoldProducts.Count())); if(user.SoldProducts.Count() != 0) foreach (var product in user.SoldProducts) { XElement pr = new XElement("product", new XAttribute("name", product.Name), new XAttribute("price", product.Price)); soldProducts.Add(pr); } xmlUser.Add(soldProducts); usersElement.Add(xmlUser); } doc.Add(usersElement); doc.Save(path); }
private static void ProductsInRange() { var db = new ShopEntities(); var products = db.Products.Where(p => p.Price >= 500 && p.Price <= 1000 && p.BuyerId == null) .OrderBy(p => p.Price) .Select(p => new { Product = p.Name, p.Price, SellerNames = p.Seller.FirstName + " " + p.Seller.LastName }); var json = JsonConvert.SerializeObject(products.ToArray()); Console.WriteLine(json); }
private static void SeedCategories() { var context = new ShopEntities(); using (StreamReader file = File.OpenText("../../../categories.json")) { Category[] categories = JsonConvert.DeserializeObject<Category[]>(file.ReadToEnd()); foreach (var category in categories) { context.Categories.AddOrUpdate(category); } context.SaveChanges(); } }
private static void CategoriesByProductsCount() { var db = new ShopEntities(); var categories = from c in db.Categories orderby c.Products.Count select new { category = c.Name, productsCount = c.Products.Count, averagePrice = db.Products.AsEnumerable().Average(a => a.Price), totalRevenue = db.Products.AsEnumerable().Sum(a => a.Price) }; var json = JsonConvert.SerializeObject(categories.ToArray()); Console.WriteLine(json); }
static void Main() { var ctx = new ShopEntities(); System.IO.Directory.CreateDirectory(folderPathAndName); //Quey 1 var specificProducts = ctx.Products .Where(p => p.Buyer == null && p.Price >= 500 && p.Price <= 1000) .OrderBy(p => p.Price) .Select(p => new { name = p.Name, price = p.Price, seller = p.Seller.FirstName + " " + p.Seller.LastName }); var serializedProducts = JsonConvert.SerializeObject(specificProducts, Newtonsoft.Json.Formatting.Indented); System.IO.File.WriteAllText(folderPathAndName + @"/products-in-range.json", serializedProducts); //Query 2 var specificUsers = ctx.Users .Where(u => u.ProductsSold.Count() >= 1) .OrderBy(u => u.LastName) .OrderBy(u => u.FirstName) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, soldProducts = u.ProductsSold .Where(p => p.Buyer != null) .Select(p => new { name = p.Name, price = p.Price, buyerFirstName = p.Buyer.FirstName, buyerLastName = p.Buyer.LastName }) }); var serializedUsers = JsonConvert.SerializeObject(specificUsers, Newtonsoft.Json.Formatting.Indented); File.WriteAllText(folderPathAndName + @"/users-sold-products.json", serializedUsers); //Query 3 var categories1 = ctx.Categories.ToArray(); var categories = ctx.Categories .OrderByDescending(c => c.Products.Count()) .Select( c => new { category = c.Name, productsCount = c.Products.Count(), averagePrice = c.Products.Count() == 0 ? 0 : c.Products.Average(p => p.Price), totalRevenue = c.Products.Count() == 0 ? 0 : c.Products.Sum(p => p.Price) }); var serializedCategories = JsonConvert.SerializeObject(categories, Newtonsoft.Json.Formatting.Indented); File.WriteAllText(folderPathAndName + @"/categories-by-products.json", serializedCategories); //Query 4 GenerateXmlWithData(folderPathAndName + @"/users-and-products.xml", ctx); }
private static void SeedProductCategories() { var context = new ShopEntities(); for (int i = 3; i < 47; i++) { Random rnd = new Random(); using (var dbContextTransaction4 = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( "INSERT INTO ProductCategories(Product_Id, Category_Id) VALUES (" + i + "," + rnd.Next(1, 11) + ")"); context.SaveChanges(); dbContextTransaction4.Commit(); } catch (Exception) { dbContextTransaction4.Rollback(); } } } }
private static void UsersAndProducts() { var db = new ShopEntities(); var users = db.Users.Where(u => u.SoldProducts.Any()).Select(u => new { u.FirstName, u.LastName, u.Age, products = u.SoldProducts.Select(p => new { p.Name, p.Price }) }); string fileName = "../../users.xml"; Encoding encoding = Encoding.GetEncoding("UTF-8"); using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding)) { writer.Formatting = Formatting.Indented; writer.IndentChar = '\t'; writer.Indentation = 1; 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); } writer.WriteAttributeString("last-name", user.LastName); if (user.Age != null) { writer.WriteAttributeString("age", user.Age.ToString()); } writer.WriteStartElement("sold-products"); writer.WriteAttributeString("count", user.products.Count().ToString()); foreach (var product in user.products) { writer.WriteStartElement("product"); writer.WriteAttributeString("name", product.Name); writer.WriteAttributeString("price", product.Price.ToString()); } } writer.WriteEndDocument(); } Console.WriteLine("Document {0} created.", fileName); }
private static void SuccessfullySoldProducts() { var db = new ShopEntities(); var sellers = from s in (db.Users.Where(u => u.SoldProducts.Any()) .Select(u => new { firstName = u.FirstName ?? "-", lastName = u.LastName, soldProducts = u.SoldProducts.Select(p => new { name = p.Name, price = p.Price, buyerFirstName = p.Buyer.FirstName ?? "-", buyerLastName = p.Buyer.LastName }).Where(p => p.buyerFirstName != null && p.buyerLastName != null) })) where s.soldProducts.Any() orderby s.lastName orderby s.firstName select new { s.firstName, s.lastName, s.soldProducts }; var json = JsonConvert.SerializeObject(sellers.ToArray()); Console.WriteLine(json); }
private static void SeedUsers() { var context = new ShopEntities(); var path = "..\\..\\..\\users.xml"; var doc = XDocument.Load(path); string json = JsonConvert.SerializeXNode(doc, Newtonsoft.Json.Formatting.Indented); var jsonObj = JObject.Parse(json); foreach (var u in jsonObj["users"]["user"]) { string lname = u["@last-name"].ToString(); string fname = null; if (u["@first-name"] != null) { fname = u["@first-name"].ToString(); } if (!Object.ReferenceEquals(null, u["@age"])) { int age = int.Parse((u["@age"].ToString())); using (var dbContextTransaction = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( "INSERT INTO Users(FirstName, LastName, Age) VALUES ('" + fname + "','" + lname + "'," + age + ")"); context.SaveChanges(); dbContextTransaction.Commit(); } catch (Exception) { dbContextTransaction.Rollback(); } } } else { using (var dbContextTransaction2 = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( "INSERT INTO Users(FirstName, LastName) VALUES (" + fname + "," + lname + ")"); context.SaveChanges(); dbContextTransaction2.Commit(); } catch (Exception) { dbContextTransaction2.Rollback(); } } } } }
private static void SeedProducts() { var context = new ShopEntities(); using (StreamReader file = File.OpenText("../../../products.json")) { Product[] products = JsonConvert.DeserializeObject<Product[]>(file.ReadToEnd()); Random rnd = new Random(); int buyer = rnd.Next(0, 50); int seller = 46; foreach (var product in products) { double price = Double.Parse((product.Price).ToString()); string name = product.Name.ToString(); if (buyer >= 3 && buyer <= 46) { using (var dbContextTransaction3 = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( "INSERT INTO Products(Name, Price, BuyerId, SellerId) VALUES ('" + name + "'," + price + "," + buyer + "," + seller + ")"); context.SaveChanges(); dbContextTransaction3.Commit(); } catch (Exception) { dbContextTransaction3.Rollback(); } } } else { using (var dbContextTransaction5 = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( "INSERT INTO Products(Name, Price, SellerId) VALUES ('" + name + "'," + price + "," + seller + ")"); context.SaveChanges(); dbContextTransaction5.Commit(); } catch (Exception) { dbContextTransaction5.Rollback(); } } } buyer = rnd.Next(0, 50); seller--; } } }