public static void Insert() { var context = new ProductsShopEntities(); var loadUsersXml = XDocument.Load("../../../users.xml"); var getAttributesValue = from u in loadUsersXml.Descendants("users").Elements("user") where u.Attribute("age") != null select new { FirstName = u.Attribute("first-name") != null?u.Attribute("first-name").Value : null, LastName = u.Attribute("last-name").Value, Age = u.Attribute("age").Value }; foreach (var getAttributeValue in getAttributesValue) { var newUser = new User() { FirstName = getAttributeValue.FirstName, LastName = getAttributeValue.LastName, Age = int.Parse(getAttributeValue.Age) }; context.Users.Add(newUser); } context.SaveChanges(); }
internal static void fourthQuery() { var context = new ProductsShopEntities(); var users = context.Users .Where(u => u.SoldProducts.Count > 0) .OrderByDescending(u => u.SoldProducts.Count) .ThenBy(u => u.LastName) .Select(u => new { u.FirstName, u.LastName, u.Age, ProductsSold = u.SoldProducts.Select(sp => new { sp.Price, sp.Name }) }); XmlDocument userDoc = new XmlDocument(); XmlDeclaration xmlDeclaration = userDoc.CreateXmlDeclaration("1.0", "UTF-8", null); XmlElement rootNode = userDoc.CreateElement("users"); rootNode.SetAttribute("count", users.Count().ToString()); userDoc.InsertBefore(xmlDeclaration, userDoc.DocumentElement); userDoc.AppendChild(rootNode); foreach (var user in users) { XmlElement userNode = userDoc.CreateElement("user"); if (user.FirstName != null) { userNode.SetAttribute("first-name", user.FirstName); } userNode.SetAttribute("last-name", user.LastName); if (user.Age != null) { userNode.SetAttribute("age", user.Age.ToString()); } XmlElement productsNode = userDoc.CreateElement("sold-products"); productsNode.SetAttribute("count", user.ProductsSold.Count().ToString()); foreach (var products in user.ProductsSold) { XmlElement proNode = userDoc.CreateElement("product"); proNode.SetAttribute("name", products.Name); proNode.SetAttribute("price", products.Price.ToString("F")); productsNode.AppendChild(proNode); } userNode.AppendChild(productsNode); rootNode.AppendChild(userNode); } userDoc.Save(@"..\..\fourthQuery.xml"); }
public static void thirdQuery() { var context = new ProductsShopEntities(); var categories = context.Categories .OrderBy(c => c.Products.Count) .Select(c => new { category = c.Name, productsCount = c.Products.Count, averagePrice = c.Products.Average(p => p.Price), totalRevenue = c.Products.Sum(p => p.Price) }); var serCategories = JsonConvert.SerializeObject(categories, Formatting.Indented); System.IO.File.WriteAllText(@"..\..\thirdQueryExport.json", serCategories); }
public static void firstQuery() { var context = new ProductsShopEntities(); var products = context.Products .Where(p => p.Price >= 500 && p.Price <= 1000 && p.BuyerId == null) .OrderBy(p => p.Price) .Select(p => new { p.Name, p.Price, Seller = (p.Seller.FirstName + " " + p.Seller.LastName).TrimStart() }); var serProducts = JsonConvert.SerializeObject(products, Formatting.Indented); System.IO.File.WriteAllText(@"..\..\firstQueryExport.json", serProducts); }
public static void Insert() { var context = new ProductsShopEntities(); var json = File.ReadAllText("../../../categories.json"); var getCategories = JsonConvert.DeserializeObject <List <GetCategories> >(json); foreach (var category in getCategories) { var newCategory = new Category() { CategoryName = category.Name }; context.Categories.Add(newCategory); } context.SaveChanges(); }
public static void CategoriesByProductsCount() { var context = new ProductsShopEntities(); var categories = context.Categories; var getAllCategories = from c in categories orderby c.Products.Count select new { CategoryName = c.CategoryName, ProductsCount = c.Products.Count, AveragePrice = c.Products.Average(a => a.Price), TotalRevenue = c.Products.Sum(s => s.Price) }; var serializeCategories = JsonConvert.SerializeObject(getAllCategories, Formatting.Indented); File.WriteAllText(@"../../QueryAndExportData/JSON/categories-by-products.json", serializeCategories); Console.WriteLine(serializeCategories); }
public static void Insert() { var context = new ProductsShopEntities(); var productsToList = context.Products.ToList(); var categoriesToList = context.Categories.ToList(); var rand = new Random(); var collectCategories = new Collection <Category>(); for (int i = 0; i < productsToList.Count; i++) { var index = rand.Next(0, categoriesToList.Count); collectCategories.Add(categoriesToList[index]); productsToList[i].Categories.Add(collectCategories[i]); } context.SaveChanges(); }
public static void SuccessfullySoldProducts() { var context = new ProductsShopEntities(); var users = context.Users; var products = context.Products; var getAllUsers = from u in users join p in products on u.Id equals p.BuyerId where p.BuyerId != null orderby u.LastName, u.FirstName group p by new { LastName = p.Seller.LastName, FirstName = p.Seller.FirstName ?? "Unknown" } into g select new { FirstName = g.Key.FirstName, LastName = g.Key.LastName, SoldProducts = from pr in g select new { ProductName = pr.ProductName, Price = pr.Price, BuyerFirstName = pr.Buyer.FirstName ?? "Unknown", BuyerLastNaem = pr.Buyer.LastName } }; var serializeUsers = JsonConvert.SerializeObject(getAllUsers, Formatting.Indented); File.WriteAllText(@"../../QueryAndExportData/JSON/successfully-sold-products.json", serializeUsers); Console.WriteLine(serializeUsers); }
public static void ProductsInRange() { var context = new ProductsShopEntities(); var products = context.Products; var getAllProducts = from p in products where p.Price >= 500 && p.Price <= 1000 && p.BuyerId == null orderby p.Price select new { ProductName = p.ProductName, Price = p.Price, SellerName = p.Seller.FirstName + " " + p.Seller.LastName }; var serializeProducts = JsonConvert.SerializeObject(getAllProducts, Formatting.Indented); File.WriteAllText(@"../../QueryAndExportData/JSON/product-in-range.json", serializeProducts); Console.WriteLine(serializeProducts); }
public static void secondQuery() { var context = new ProductsShopEntities(); var users = context.Users .Where(u => u.SoldProducts.Count > 0 && u.SoldProducts.All(sp => sp.BuyerId != null)) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, soldProducts = u.SoldProducts .Select(sp => new { name = sp.Name, price = sp.Price, buyerFirstName = sp.Buyer.FirstName, buyerLastName = sp.Buyer.LastName }) }); var serUsers = JsonConvert.SerializeObject(users, Formatting.Indented); System.IO.File.WriteAllText(@"..\..\secondQueryExport.json", serUsers); }
public static void UsersAndProducts() { var context = new ProductsShopEntities(); var users = context.Users; var products = context.Products; var getAllUsers = from u in users join p in products on u.Id equals p.BuyerId where p.BuyerId != null && u.FirstName != null orderby u.LastName group p by new { p.Buyer.FirstName, p.Buyer.LastName, p.Buyer.Age } into g select new { g.Key.FirstName, g.Key.LastName, g.Key.Age, Product = from pr in g select new { pr.ProductName, pr.Price } }; var count = 0; var totalCaount = 0; var xmlUsers = new XElement("users"); foreach (var user in getAllUsers) { var xmlSingleUser = new XElement("user"); xmlSingleUser.Add(new XAttribute("first-name", user.FirstName)); xmlSingleUser.Add(new XAttribute("last-name", user.LastName)); xmlSingleUser.Add(new XAttribute("age", user.Age)); var xmlSoldProducts = new XElement("sold-products"); foreach (var product in user.Product) { var xmlProduct = new XElement("product"); xmlProduct.Add(new XAttribute("name", product.ProductName)); xmlProduct.Add(new XAttribute("price", product.Price)); xmlSoldProducts.Add(xmlProduct); count++; totalCaount++; } xmlSoldProducts.Add(new XAttribute("count", count)); xmlSingleUser.Add(xmlSoldProducts); xmlUsers.Add(xmlSingleUser); count = 0; } xmlUsers.Add(new XAttribute("count", totalCaount)); Console.WriteLine(xmlUsers); var xmlDoc = new XDocument(xmlUsers); xmlDoc.Save("../../QueryAndExportData/XML/users-and-products.xml"); }
public static void Insert() { var context = new ProductsShopEntities(); var json = File.ReadAllText("../../../products.json"); var getProducts = JsonConvert.DeserializeObject <List <GetProducts> >(json); //Get the first and last Users Id var firstId = context.Users.First().Id; var lastId = context.Users.OrderByDescending(i => i.Id).First().Id; //Insert, randomly, into array the BuyerId value var rand = new Random(); var nullIndex = 1; var buyerArray = new int?[getProducts.Count]; for (var i = 0; i < getProducts.Count; i++) { buyerArray[i] = rand.Next(firstId, lastId); nullIndex++; //Every 10th row will have a null value if (nullIndex != 10) { continue; } buyerArray[i] = null; nullIndex = 1; } //Insert, randomly, into array the SellerId value var sellerArray = new int[getProducts.Count]; for (var i = 0; i < getProducts.Count; i++) { sellerArray[i] = rand.Next(firstId, lastId); } //Insert into Products var index = 0; foreach (var product in getProducts) { var newProduct = new Product() { ProductName = product.Name, Price = product.Price, SellerId = sellerArray[index], BuyerId = buyerArray[index] }; context.Products.Add(newProduct); index++; } context.SaveChanges(); }
static void Main(string[] args) { var migrationStrategy = new MigrateDatabaseToLatestVersion <ProductsShopEntities, Configuration>(); Database.SetInitializer(migrationStrategy); var context = new ProductsShopEntities(); //Query 1 - Products In Range //var products = context.Products // .Where(p => p.Buyer == null && p.Price >= 500m && p.Price <= 1000m) // .OrderBy(p => p.Price) // .Select(p => new // { // p.Name, // p.Price, // Seller = (p.Seller.FirstName != null ? p.Seller.FirstName + " " : "") + p.Seller.LastName // }); //var json = JsonConvert.SerializeObject(products, Formatting.Indented); //Console.WriteLine(json); //Console.ReadLine(); //Query 2 - Successfully Sold Products //var sellers = context.Users // .Where(u => u.ProductsSold.Any(p => p.Buyer != null)) // .OrderBy(u => u.LastName) // .ThenBy(u => u.FirstName) // .Select(u => new // { // u.FirstName, // u.LastName, // ProductsSold = u.ProductsSold // .Where(p => p.Buyer != null) // .Select(p => new // { // p.Name, // p.Price, // BuyerFirstName = p.Buyer.FirstName != null ? p.Buyer.FirstName : "", // BuyerLastName = p.Buyer.LastName // }) // }); //var json = JsonConvert.SerializeObject(sellers, Formatting.Indented); //Console.WriteLine(json); //Console.ReadLine(); //Categories By Products Count //var categories = context.Categories // .OrderByDescending(c => c.Products.Count()) // .Select(c => new // { // c.Name, // ProductsCount = c.Products.Count(), // AveragePrice = c.Products.Average(p => p.Price), // TotalRevenye = c.Products.Sum(p => p.Price) // }); //var json = JsonConvert.SerializeObject(categories, Formatting.Indented); //Console.WriteLine(json); //Console.ReadLine(); //Query 4 - Users and Products //Unfinished var sellers = context.Users .Where(u => u.ProductsSold.Count() >= 1) .OrderByDescending(u => u.ProductsSold.Count()) .ThenBy(u => u.LastName) .Select(u => new { u.FirstName, u.LastName, u.Age, ProductsSold = u.ProductsSold.Select(p => new { p.Name, p.Price }) }); JArray sellersArray = new JArray(); foreach (var user in sellers) { JObject userObject = new JObject(); if (user.FirstName != null) { userObject.Add(new JProperty("@first-name", user.FirstName)); } userObject.Add(new JProperty("@last-name", user.LastName)); if (user.Age != null) { userObject.Add(new JProperty("@age", user.Age)); } JObject soldProducts = new JObject(); soldProducts.Add(new JProperty("@count", user.ProductsSold.Count())); JArray products = new JArray(); foreach (var product in user.ProductsSold) { JObject productObject = new JObject(); productObject.Add(new JProperty("@price", product.Price)); productObject.Add(new JProperty("@name", product.Name)); products.Add(productObject); } soldProducts.Add(new JProperty("product", products)); userObject.Add(new JProperty("sold-products", soldProducts)); sellersArray.Add(userObject); } JObject users = new JObject(); users.Add(new JProperty("@count", sellers.Count())); users.Add(new JProperty("user", sellersArray)); JObject main = new JObject(); main.Add(new JProperty("?xml", new JObject(new JProperty("@version", "1.0"), new JProperty("@encoding", "utf-8")))); main.Add(new JProperty("users", users)); var json = JsonConvert.SerializeObject(main, Formatting.Indented); XDocument xmlFromJson = JsonConvert.DeserializeXNode(json); xmlFromJson.Save("../../../../../sellers.xml"); //Console.WriteLine(main); }