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));
}
