private static void SuccessfullySoldProducts(ProductsShopContext context) { const string path = ExportPath + "sold_products.json"; var users = context.Users .Where(u => u.ProductsSold.Any(p => p.Buyer != null)) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { firstName = u.FirstName ?? string.Empty, lastName = u.LastName, soldProducts = u.ProductsSold.Where(p => p.Buyer != null).Select(p => new { name = p.Name ?? string.Empty, price = p.Price, buyerFirstName = p.Buyer.FirstName, buyerLastName = p.Buyer.LastName }) }); var json = JsonConvert.SerializeObject(users, Formatting.Indented); File.WriteAllText(path, json); Console.WriteLine("Successfully Sold Products file path:\n {0}\n", Path.GetFullPath(path)); }
private static void SeedDataBase(ProductsShopContext context) { Random rnd = new Random(); SeedUsers(context); SeedProducts(context, rnd); SeedCategories(context, rnd); }
private static void ResetDatabase() { using (var context = new ProductsShopContext()) { context.Database.EnsureDeleted(); context.Database.EnsureCreated(); } }
private static void ExtractUsersAndProducts(ProductsShopContext context) { var users = context.Users.Where(u => u.SoldProducts.Any(p => p.Buyer != null)) .Select( u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, soldProducts = u.SoldProducts.Where(p => p.Buyer != null) .Select(p => new { productName = p.Name, price = p.Price }) }) .OrderByDescending(u => u.soldProducts.Count()) .ThenBy(u => u.lastName); var doc = new XDocument(); var rootNode = new XElement("users"); rootNode.SetAttributeValue("count", users.Count()); foreach (var u in users) { var user = new XElement("user"); if (u.firstName != null) { user.SetAttributeValue("first-name", u.firstName); } user.SetAttributeValue("last-name", u.lastName); if (u.age != null) { user.SetAttributeValue("age", u.age.Value); } var soldProducts = new XElement("sold-products"); soldProducts.SetAttributeValue("count", u.soldProducts.Count()); foreach (var product in u.soldProducts) { var productName = new XElement("product"); productName.SetAttributeValue("name", product.productName); productName.SetAttributeValue("price", product.price); soldProducts.Add(productName); } user.Add(soldProducts); rootNode.Add(user); } doc.Add(rootNode); doc.Save("../../../ query4.xml"); }
static string ImportCategoriesFromJson(ProductsShopContext context) { Category[] categories = ImportJson <Category>("../../../Resources/categories.json"); context.Categories.AddRange(categories); context.SaveChanges(); return("Successfully added categories to the database."); }
static void Main(string[] args) { using (var context = new ProductsShopContext()) { var result = GetCategoriesByProductsCount(context); Console.WriteLine(result); } }
private void SeedCategories(ProductsShopContext context, string categoriesPath) { var deserializedCategories = JsonConvert.DeserializeObject <List <Category> >(File.ReadAllText(categoriesPath)); foreach (var category in deserializedCategories) { context.Categories.Add(category); } }
static void Main(string[] args) { var context = new ProductsShopContext(); ExportProductsInRange(context); ExportSoldProducts(context); ExportCategoriesByProductCount(context); ExportUsersAndProducts(context); }
private static void GetSoldProductsXml() { using (var context = new ProductsShopContext()) { var users = context .Users .Where(u => u.SoldProducts.Count > 0) .Include(u => u.SoldProducts) .ThenInclude(p => p.Buyer) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, soldProducts = u.SoldProducts .Select(p => new { name = p.Name, price = p.Price, }).ToList() }) .OrderBy(u => u.lastName) .ThenBy(u => u.firstName) .ToList(); var xDocument = new XDocument(); xDocument.Add(new XElement("users")); foreach (var user in users) { var products = new List <XElement>(); foreach (var soldProduct in user.soldProducts) { products.Add(new XElement("product", new XAttribute("name", soldProduct.name), new XAttribute("price", soldProduct.price))); } if (user.firstName is null) { xDocument.Root.Add( new XElement("user", new XAttribute("lastName", user.lastName)), new XElement("sold-products", products)); } else { xDocument.Root.Add(new XElement("user", new XAttribute("firstName", user.firstName), new XAttribute("lastName", user.lastName)), new XElement("sold-products", products)); } } var writer = new StreamWriter("soldproducts.xml"); using (writer) { xDocument.Save(writer); } } }
static void Main(string[] args) { using (var context = new ProductsShopContext()) { var result = GetProductsInRange(context); Console.WriteLine(result); } }
static void Main(string[] args) { ProductsShopContext ctx = new ProductsShopContext(); ctx.Database.Initialize(true); XMLMethods.SeedData(ctx); XMLMethods.ExportData(ctx); }
private static void ImportUsers() { var context = new ProductsShopContext(); var json = File.ReadAllText(usersPath); var userEntity = JsonConvert.DeserializeObject <IEnumerable <User> >(json); context.Users.AddRange(userEntity); context.SaveChanges(); }
public static void SeedCategories(ProductsShopContext context) { var json = File.ReadAllText(Paths.path + Paths.categories); var categories = JsonConvert.DeserializeObject <Category[]>(json); context.Categories.AddRange(categories); context.SaveChanges(); }
public static void SeedUsers(ProductsShopContext context) { var json = File.ReadAllText(Paths.path + Paths.users); var users = JsonConvert.DeserializeObject <User[]>(json); context.Users.AddRange(users); context.SaveChanges(); }
private static void SoldProducts() { Console.WriteLine("Solution to Query 2 - Successfully Sold Products"); using (ProductsShopContext context = new ProductsShopContext()) { // Users & sold products var users = context.Users .Where(u => u.ProductsSold.Any()) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { FirstName = u.FirstName, // optional LastName = u.LastName, SoldProducts = u.ProductsSold.Select(p => new { Name = p.Name, Price = p.Price }) }); // Create XML Document XDocument xmlDoc = new XDocument(); XElement usersXml = new XElement("users"); foreach (var user in users) { // User XElement userXml = new XElement("user"); if (user.FirstName != null) { userXml.Add(new XAttribute("first-name", user.FirstName)); // no attribute if null } userXml.Add(new XAttribute("last-name", user.LastName)); // required // Sold products XElement soldProductsXml = new XElement("sold-products"); foreach (var product in user.SoldProducts) { XElement productXml = new XElement("product", new XElement("name", product.Name), new XElement("price", product.Price)); soldProductsXml.Add(productXml); } // Add sold products to user userXml.Add(soldProductsXml); // Add user to users usersXml.Add(userXml); } xmlDoc.Add(usersXml); // Exmport & Print ExportAndPrint(xmlDoc, "users-sold-products"); } }
static void Main(string[] args) { ProductsShopContext context = new ProductsShopContext(); context.Products.FirstOrDefault(); //Task 1: Create Database //ImportUsers(context); //AddProducts(context); //AddCategories(context); //AddingCategoriesToProducts(context); //Task 2 Products In Range //ProductsInRange(context); //Task 3 Successfully Sold Products //SuccessfullySoldProducts(context); //Task 4 Categories By Products Count //var categories = context.Categories.Select(y => new { Name = y.Name, NumberOfProducts = y.Products.Count, AveragePriceOfProducts = y.Products.Average(x => x.Price), TotalRevenue = y.Products.Sum(x => x.Price) }).OrderBy(x => x.NumberOfProducts); //foreach (var element in categories) //{ // var planetsAsJson = JsonConvert.SerializeObject(element, Formatting.Indented); // Console.WriteLine(planetsAsJson); //} // //Task 5 Users and Products //var users = // context.Users.Where(x => x.SoldProducts.Any()) // .OrderByDescending(x => x.SoldProducts.Count) // .ThenBy(y => y.LastName) // .Select( // x => new // { // FirstName = x.FirstName, // LastName = x.LastName, // Age = x.Age, // Products = // x.SoldProducts.Select(y => new {ProductName = y.Name, ProductPrice = y.Price}) // }); //Task 6 Users and Products //GetUsers(context); }
//ResetDb(pick XML or JSON)! private static void ResetDatabase() { using (var db = new ProductsShopContext()) { db.Database.EnsureDeleted(); db.Database.EnsureCreated(); //JsonImport(); //XmlImport(); } }
static string ImportUsersFromJson(ProductsShopContext context) { User[] users = ImportJson <User>("../../../Resources/users.json"); context.Users.AddRange(users); context.SaveChanges(); return("Successfully added users to the database"); }
static void Main() { var context = new ProductsShopContext(); //InitializeDatabase(context); //SeedDatabase(context); ExecuteXmlQueries(context); }
private static void ResetDatabase(Importer importer) { using (var context = new ProductsShopContext()) { context.Database.EnsureDeleted(); context.Database.Migrate(); } importer.Import(); }
private static void ImportUsersToDb(ProductsShopContext context) { Console.WriteLine("Importing users.."); string jsnoFile = File.ReadAllText(@"..\..\Import\users.json"); List <User> users = JsonConvert.DeserializeObject <List <User> >(jsnoFile); context.Users.AddRange(users); context.SaveChanges(); }
static void Main(string[] args) { var context = new ProductsShopContext(); var count = context.Users.Count(); ExtractProductsInRage(context, 500, 1000); ExtractUsersWithSoldProducts(context); ExtractCategoriesBuProductsCount(context); ExtractUsersAndProducts(context); }
private static void ImportCategories(ProductsShopContext context) { Console.WriteLine("Importing categories.."); string jsnoFile = File.ReadAllText(@"..\..\Import\categories.json"); List <Category> categories = JsonConvert.DeserializeObject <List <Category> >(jsnoFile); context.Categories.AddRange(categories); context.SaveChanges(); }
private static void ImportProductsFromXml(ProductsShopContext context) { const string path = "Resources/products.xml"; var xmlString = File.ReadAllText(path); var xml = XDocument.Parse(xmlString); var root = xml.Root.Elements(); var products = new List <Product>(); var rnd = new Random(); var usersIds = context.Users.Select(u => u.Id).ToArray(); foreach (var xElement in root) { var name = xElement.Element("name").Value; var price = decimal.Parse(xElement.Element("price").Value); var product = new Product { Name = name, Price = price }; var sellerIndex = rnd.Next(0, usersIds.Length); var sellerId = usersIds[sellerIndex]; var buyerIndex = rnd.Next(0, usersIds.Length); int?buyerId = usersIds[buyerIndex]; while (sellerId == buyerId) { buyerIndex = rnd.Next(0, usersIds.Length); buyerId = usersIds[buyerIndex]; } if (buyerId - sellerId < 5 && buyerId - sellerId > 0) { buyerId = null; } product.SellerId = sellerId; product.BuyerId = buyerId; products.Add(product); } context.Products.AddRange(products); context.SaveChanges(); Console.WriteLine($"{products.Count} products were imported from file: {path}"); }
public override string Execute(ProductsShopContext context) { string xmlFilePath = Helpers.TryLocateFileForImport(); XDocument xDoc = XDocument.Load(xmlFilePath); XElement[] usersFromXml = xDoc.Root?.Elements().ToArray(); ICollection <User> users = new HashSet <User>(); if (usersFromXml != null) { try { foreach (XElement u in usersFromXml) { string firstName = u.Attribute("firstName")?.Value; string lastName = u.Attribute("lastName")?.Value; int? age = null; if (u.Attribute("age")?.Value != null) { age = int.Parse(u.Attribute("age").Value); } User currentUser = new User { FirstName = firstName, LastName = lastName, Age = age }; users.Add(currentUser); } context.Users.AddRange(users); context.SaveChanges(); string result = string.Format(Messages.SeveralEntitiesImportedFromFile, users.Count, nameof(users), xmlFilePath); if (users.Count == 1) { result = string.Format(Messages.OneEntityImportedFromFile, "user", xmlFilePath); } return(result); } catch (Exception) { throw new InvalidOperationException(Messages.InvalidXmlStructure); } } return(Messages.XmlEmpty); }
private static void UsersAndProducts(ProductsShopContext context) { var users = context.Users .Where(u => u.ProductsSold.Count > 0) .OrderByDescending(u => u.ProductsSold.Count) .ThenBy(u => u.LastName) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, products = u.ProductsSold .Select(ps => new { name = ps.Name, price = ps.Price }) }); XElement usersElement = new XElement("users", new XAttribute("count", users.Count())); foreach (var user in users) { XElement userElement = new XElement("user"); if (user.firstName != null) { userElement.Add(new XAttribute("first-name", user.firstName)); } userElement.Add(new XAttribute("last-name", user.lastName)); if (user.age != null) { userElement.Add(new XAttribute("age", user.age)); } XElement soldProductsElement = new XElement("sold-products", new XAttribute("count", user.products.Count())); foreach (var product in user.products) { XElement productElement = new XElement("product", new XAttribute("name", product.name), new XAttribute("price", product.price)); soldProductsElement.Add(productElement); } userElement.Add(soldProductsElement); usersElement.Add(userElement); } usersElement.Save("../../../users-and-products.xml"); }
public static string ImportProductsFromXml() { var path = "Files/products.xml"; var xmlString = File.ReadAllText(path); var xmlDoc = XDocument.Parse(xmlString); var xmlElement = xmlDoc.Root.Elements(); using (var db = new ProductsShopContext()) { var userIds = db.Users.Select(e => e.Id).ToList(); var categoryIds = db.Categories.Select(e => e.Id).ToList(); var categoryProducts = new List <CategoryProduct>(); foreach (var e in xmlElement) { var name = e.Element("name").Value; var price = decimal.Parse(e.Element("price").Value); var random = new Random(); var sellerId = random.Next(1, userIds.Count + 1); int?buyerId = random.Next(1, userIds.Count + 1); if (buyerId == sellerId || buyerId - sellerId < 8) { buyerId = null; } var product = new Product { Name = name, Price = price, SellerId = sellerId, BuyerId = buyerId, }; var categoryId = random.Next(1, categoryIds.Count + 1); var categoryProduct = new CategoryProduct { Product = product, CategoryId = categoryId }; categoryProducts.Add(categoryProduct); } db.CategoryProducts.AddRange(categoryProducts); db.SaveChanges(); return($"{categoryProducts.Count} products has been inserted from file: {path}"); } }
private static void UsersAndProducts(ProductsShopContext context) { var users = context.Users .Where(u => u.ProductsSold.Count > 0) .OrderByDescending(u => u.ProductsSold.Count) .ThenBy(u => u.LastName) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, products = u.ProductsSold .Select(ps => new { name = ps.Name, price = ps.Price }) }); XElement usersElement = new XElement("users", new XAttribute("count", users.Count())); foreach (var user in users) { XElement userElement = new XElement("user"); if (user.firstName != null) { userElement.Add(new XAttribute("first-name", user.firstName)); } userElement.Add(new XAttribute("last-name", user.lastName)); if (user.age != null) { userElement.Add(new XAttribute("age", user.age)); } XElement soldProductsElement = new XElement("sold-products", new XAttribute("count", user.products.Count())); foreach (var product in user.products) { XElement productElement = new XElement("product", new XAttribute("name", product.name), new XAttribute("price", product.price)); soldProductsElement.Add(productElement); } userElement.Add(soldProductsElement); usersElement.Add(userElement); } usersElement.Save("../../../users-and-products.xml"); }
static void Main(string[] args) { var fileJson = @"C:\users.json"; using (var context = new ProductsShopContext()) { string result = ImportUsers(context, fileJson); Console.WriteLine(result); } }
private static void UsersAndProductsXML(ProductsShopContext context) { var users = context.Users .Where(u => u.SellingProducts.Count > 0) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, soldProducts = new { count = u.SellingProducts.Count, products = u.SellingProducts .Select(sp => new { name = sp.Name, price = sp.Price }) .ToArray() } }) .OrderByDescending(o => o.soldProducts.count) .ThenBy(o => o.lastName) .ToArray(); var xDoc = new XDocument(); xDoc.Add(new XElement("users")); xDoc.Element("users").SetAttributeValue("count", users.Count()); foreach (var u in users) { var user = new XElement("user"); user.SetAttributeValue("first-name", u.firstName); user.SetAttributeValue("last-name", u.lastName); user.SetAttributeValue("age", u.age); var soldProducts = new XElement("sold-products"); soldProducts.SetAttributeValue("count", u.soldProducts.count); foreach (var p in u.soldProducts.products) { var product = new XElement("product", new XAttribute("name", p.name), new XAttribute("price", p.price)); soldProducts.Add(product); } user.Add(soldProducts); xDoc.Element("users").Add(user); } xDoc.Save("XML/UsersAndProducts.xml"); }
static void Main(string[] args) { using (var context = new ProductsShopContext()) { var jsonFile = @"C:\categories-products.json"; var result = ImportCategoryProducts(context, jsonFile); Console.WriteLine(result); } }
private static void SeedOneCategoryToDataBase(ProductsShopContext context, XElement categoryNote) { string name = categoryNote.Element("name").Value; Category category = new Category() { Name = name }; context.Categories.Add(category); }
public static void Main() { var context = new ProductsShopContext(); ExportFirstQueryResult(context, 500, 1000); ExportSuccessfullySoldProducts(context); CategoriesByProductsCount(context); UsersBySoldProducts(context); }
public static void Main() { if (!Directory.Exists(ExportPath)) { Directory.CreateDirectory(ExportPath); } var context = new ProductsShopContext(); ProductsInPriceRange(context, 500, 1000); SuccessfullySoldProducts(context); GetAllCategories(context); UsersAndProducts(context); }
private static void UsersBySoldProducts(ProductsShopContext context) { var result = from user in context.Users where user.ProductsSold.Count >= 2 orderby user.ProductsSold.Count descending, user.LastName ascending select new { user.FirstName, user.LastName, user.Age, SoldProducts = user.ProductsSold.Select(p => new { p.Name, p.Price }) }; var doc = new XDocument(new XElement("users", new XAttribute("count", result.Count()))); doc.Declaration = new XDeclaration("1.0", "utf-8", null); foreach (var user in result) { var userEl = new XElement("user", new XAttribute("last-name", user.LastName)); if (user.FirstName != null) { userEl.Add(new XAttribute("first-name", user.FirstName)); } if (user.Age != null) { userEl.Add(new XAttribute("age", user.Age)); } var userSoldProducts = new XElement("sold-products", new XAttribute("count", user.SoldProducts.Count())); foreach (var product in user.SoldProducts) { userSoldProducts.Add(new XElement("product", new XAttribute("name", product.Name), new XAttribute("price", product.Price))); } userEl.Add(userSoldProducts); doc.Root.Add(userEl); } doc.Save("../../../_4_Users-by-products-sold.xml"); }
private static void CategoriesByProductsCount(ProductsShopContext context) { var result = from category in context.Categories orderby category.Products.Count select new { category.Name, category.Products.Count, AveragePrice = category.Products.Average(p => p.Price), TotalRavenue = category.Products.Sum(p => p.Price) }; var resultText = JsonConvert.SerializeObject(result.ToList()); File.WriteAllText("../../../_3_Categories=by-products-count.json", resultText); }
private static void CategoriesByProductsCount(ProductsShopContext context, JavaScriptSerializer serializer) { var categories = context.Categories .Select(c => new { Name = c.Name, ProductsCount = c.Products.Count, AveragePrice = c.Products.Average(p => (decimal?)p.Price) ?? 0, TotalPrice = c.Products.Sum(p => (decimal?)p.Price) ?? 0 }) .OrderByDescending(c => c.ProductsCount); var categoriesJson = serializer.Serialize(categories); File.WriteAllText("../../../Query-3.CategoriesByProductsCount.json", categoriesJson); }
private static void ExportFirstQueryResult(ProductsShopContext context, decimal minPrice, decimal maxPrice) { var result = from product in context.Products where product.Price <= maxPrice && product.Price >= minPrice orderby product.Price select new { product.Name, product.Price, FullName = product.Seller.FirstName + " " + product.Seller.LastName }; var resultText = JsonConvert.SerializeObject(result.ToList()); File.WriteAllText("../../../_1_Products-in-price-range.json", resultText); }
private static void ProductsInPriceRange(ProductsShopContext context, decimal lowerLimit, decimal upperLimit) { const string path = ExportPath + "products_in_range.json"; var products = context.Products .Where(p => p.Price >= lowerLimit && p.Price <= upperLimit && p.Buyer == null) .OrderBy(p => p.Price) .Select(p => new { name = p.Name, price = p.Price, seller = (p.Seller.FirstName != null ? p.Seller.FirstName + " " : "") + p.Seller.LastName }); var json = JsonConvert.SerializeObject(products, Formatting.Indented); File.WriteAllText(path, json); Console.WriteLine("Products in range file path:\n {0}\n", Path.GetFullPath(path)); }
static void Main() { var context = new ProductsShopContext(); var serializer = new JavaScriptSerializer(); // Problem 3 // Query 1 // ProductsInRange(context, serializer); // Query 2 // SuccessfullySoldProducts(context, serializer); // Query 3 // CategoriesByProductsCount(context, serializer); // Query 4 UsersAndProducts(context); }
private static void GetAllCategories(ProductsShopContext context) { const string path = ExportPath + "categories_by_products_count.json"; var categories = context.Categories .OrderByDescending(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 json = JsonConvert.SerializeObject(categories, Formatting.Indented); File.WriteAllText(path, json); Console.WriteLine("Categories By Products Count file path:\n {0}\n", Path.GetFullPath(path)); }
private static void ExportSuccessfullySoldProducts(ProductsShopContext context) { var result = from user in context.Users where user.ProductsSold.Any(sp => sp.Buyer != null) orderby user.LastName, user.FirstName select new { user.FirstName, user.LastName, SoldProducts = user.ProductsSold .Select(p => new { p.Name, p.Price, p.Buyer.FirstName, p.Buyer.LastName }) }; var resultText = JsonConvert.SerializeObject(result.ToList()); File.WriteAllText("../../../_2_Successfully-sold-products.json", resultText); }
static void Main() { var context = new ProductsShopContext(); var testCount = context.Users.Count(); //Problem 3 ------------------------------------------------------------------------------------ //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.Export the result to JSON var productsInRange = context.Products .Include(x => x.Seller) .Include(x => x.Buyer) .Where(x => x.Price >= 500 && x.Price <= 1000 && x.BuyerId == null) .OrderBy(x => x.Price) .Select(x => new { ProductName = x.Name, x.Price, SellerFullName = x.Seller.FirstName + x.Seller.LastName }); string json = JsonConvert.SerializeObject(productsInRange.ToArray()); System.IO.File.WriteAllText("../../../products-in-range.json", json); //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. var usersWithSoldItem = context.Users .Include(x => x.ProductsSold) .Where(x => x.ProductsSold.Count() > 0) .Select(x => new { firstName = x.FirstName ?? "", lastName = x.LastName, soldProducts = x.ProductsSold.Select(p => new { name = p.Name, price = p.Price, buyerFirstName = p.Buyer.FirstName ?? "", buyerLastName = p.Buyer.LastName }) }); string json2 = JsonConvert.SerializeObject(usersWithSoldItem); System.IO.File.WriteAllText("../../../users-sold-products.json", json2); //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). var categoriesByProducts = context.Categorys .Include(x => x.Products) .Select(x => new { category = x.Name, productsCount = x.Products.Count, averagePrice = x.Products.Average(z => (Decimal?) z.Price), totalRevenue = x.Products.Sum(q => (Decimal?) q.Price) }); string json3 = JsonConvert.SerializeObject(categoriesByProducts); System.IO.File.WriteAllText("../../../categories-by-products.json", json3); //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.Follow the format below to better //understand how to structure your data. Note: If a user has no first name or age, do not add attributes. var usersAndProducs = context.Users .Include(x => x.ProductsSold) .Where(x => x.ProductsSold.Count() > 0) .Select(x => new { firstName = x.FirstName ?? "", lastName = x.LastName, age = x.Age ?? 0, soldProducts = x.ProductsSold.Select(p => new { name = p.Name, price = p.Price, }) }) .OrderByDescending(x => x.soldProducts.Count()) .ThenBy(x => x.lastName); string path = "../../../users-and-products.xml"; Encoding encoding = Encoding.GetEncoding("utf-8"); using (var writer = new XmlTextWriter(path, encoding)) { writer.Formatting = System.Xml.Formatting.Indented; writer.IndentChar = '\t'; writer.Indentation = 1; writer.WriteStartDocument(); writer.WriteStartElement("users"); writer.WriteAttributeString("count", usersAndProducs.Count().ToString()); foreach (var user in usersAndProducs) { writer.WriteStartElement("user"); if (user.firstName != "" || user.firstName != null) { writer.WriteAttributeString("first-name", user.firstName); } writer.WriteAttributeString("last-name", user.lastName); if (user.age != 0) { writer.WriteAttributeString("age", user.age.ToString()); } writer.WriteStartElement("sold-products"); writer.WriteAttributeString("count", user.soldProducts.Count().ToString()); foreach (var product in user.soldProducts) { writer.WriteStartElement("product "); writer.WriteAttributeString("name", product.name); writer.WriteAttributeString("price", product.price.ToString()); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); } } }
private static void SuccessfullySoldProducts(ProductsShopContext context, JavaScriptSerializer serializer) { var users = context.Users .Where(u => u.ProductsSold.Count > 0) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, ProductsSold = u.ProductsSold .Where(ps => ps.Buyer != null) .Select(ps => new { Name = ps.Name, Price = ps.Price, buyerFirstName = ps.Buyer.FirstName, buyerLastName = ps.Buyer.LastName }) }) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName); var usersJson = serializer.Serialize(users); File.WriteAllText("../../../Query-2.SuccessfullySoldProducts.json", usersJson); }
public static void ProductsInRange(ProductsShopContext context, JavaScriptSerializer serializer) { var products = context.Products .Where(p => p.Buyer == null && (p.Price > 500 && p.Price < 1000)) .Select(p => new { Name = p.Name, Price = p.Price, Seller = p.Seller.FirstName + " " + p.Seller.LastName }) .OrderBy(p => p.Price) .ToList(); string productsJson = serializer.Serialize(products); File.WriteAllText(@"../../../Query-1.productsInRange.json", productsJson); }
private static void UsersAndProducts(ProductsShopContext context) { const string path = ExportPath + "users_and_products.xml"; var users = context.Users .Where(u => u.ProductsSold.Any(p => p.Buyer != null)) .OrderByDescending(u => u.ProductsSold.Count) .ThenBy(u => u.LastName) .Select(u => new { u.FirstName, u.LastName, u.Age, soldProducts = u.ProductsSold.Where(p => p.Buyer != null).Select(p => new { p.Name, p.Price }) }).ToList(); var doc = new XDocument(); var rootNode = new XElement("users"); rootNode.SetAttributeValue("count", users.Count); foreach (var user in users) { var userNode = new XElement("user"); if (user.FirstName != null) { userNode.SetAttributeValue("first-name", user.FirstName); } userNode.SetAttributeValue("last-name", user.LastName); if (user.Age != null) { userNode.SetAttributeValue("age", user.Age); } var soldProductsNode = new XElement("sold-products"); soldProductsNode.SetAttributeValue("count", user.soldProducts.Count()); foreach (var product in user.soldProducts) { var productNode = new XElement("product"); productNode.SetAttributeValue("name", product.Name); productNode.SetAttributeValue("price", product.Price); soldProductsNode.Add(productNode); } userNode.Add(soldProductsNode); rootNode.Add(userNode); } doc.Add(rootNode); doc.Save(path); Console.WriteLine("Users And Products file path:\n {0}\n", Path.GetFullPath(path)); }