private static void SeedCategories(ProductsShopContext context, Random rnd)
{
var json = File.ReadAllText(@"../../SeedData/categories.json");
JavaScriptSerializer serializer = new JavaScriptSerializer();
var categories = serializer.Deserialize<CategoryDTO[]>(json);
var products = context.Products.ToArray();
foreach (var category in categories)
{
Category currCategory = new Category()
{
Name = category.Name
};
context.Categories.Add(currCategory);
context.SaveChanges();
}
var allCategories = context.Categories.ToArray();
for (int i = 0; i < products.Length * 2; i++)
{
int productIndex = rnd.Next(0, products.Length);
var product = products[productIndex];
var categoryIndex = rnd.Next(0, allCategories.Length);
var category = allCategories[categoryIndex];
product.Categories.Add(category);
context.SaveChanges();
}
}
static void Main(string[] args)
{
var context = new ProductsShopContext();
Console.WriteLine(context.Users.Count());
}
private static void SeedProducts(ProductsShopContext context, Random rnd)
{
var json = File.ReadAllText(@"../../SeedData/products.json");
JavaScriptSerializer serializer = new JavaScriptSerializer();
var products = serializer.Deserialize<ProductDTO[]>(json);
var buyers = context.Users.ToArray();
var sellers = context.Users.ToArray();
foreach (var product in products)
{
int buyerIndex = rnd.Next(1, buyers.Length);
var buyer = buyers[buyerIndex];
int sellerIndex = rnd.Next(1, sellers.Length);
var seller = sellers[sellerIndex];
Product newProduct = new Product()
{
Name = product.Name,
Price = product.Price,
Seller = seller
};
if (buyerIndex != sellerIndex)
{
newProduct.Buyer = buyer;
}
context.Products.Add(newProduct);
context.SaveChanges();
}
}
private static void UsersAndProducts(ProductsShopContext db)
{
var users = db.Users
.Where(u => u.ProductsSold.Count >= 1)
.Select(u => new
{
u.FirstName,
u.LastName,
u.Age,
Product = u.ProductsSold.Select(p => new
{
p.Name,
p.Price
})
}).OrderByDescending(u => u.Product.Count()).ThenBy(u => u.LastName);
XElement root = new XElement("users");
root.SetAttributeValue("count", users.Count());
foreach (var userNode in users)
{
var user = new XElement("user");
if (userNode.FirstName != null)
{
user.SetAttributeValue("first-name", userNode.FirstName);
}
if (userNode.LastName != null)
{
user.SetAttributeValue("last-name", userNode.LastName);
}
if (userNode.Age != null)
{
user.SetAttributeValue("age", userNode.Age);
}
var soldProducts = new XElement("sold-products");
soldProducts.SetAttributeValue("count", userNode.Product.Count());
foreach (var productNode in userNode.Product)
{
var product = new XElement("product");
if (productNode.Name != null)
{
product.SetAttributeValue("name", productNode.Name);
}
if (productNode.Price != null)
{
product.SetAttributeValue("price", productNode.Price);
}
soldProducts.Add(product);
}
user.Add(soldProducts);
root.Add(user);
}
var xmlDoc = new XDocument(root);
xmlDoc.Save("../../../users-and-product.xml");
}
static void Main()
{
var context = new ProductsShopContext();
SeedUsers(context);
Random rnd = new Random();
SeedProducts(context, rnd);
SeedCategories(context, rnd);
}
static void Main()
{
var context = new ProductsShopContext();
//Problem 1. Get all products in a specified price range (500 to 1000) which have no buyer
ProductsWithNoBuyerInRange(context, 500, 1000);
//Problem 2. Successfully Sold Products
SuccessfullySoldProducts(context);
//Problem 3. Categories By Products Count
CategoriesByProductsCount(context);
//Problem 4. Users and Products
UsersAndProducts(context);
}
private static void CategoryByProductsToJSONFile(ProductsShopContext db)
{
var categories = db.Categories
.Select(c => new
{
category = c.Name,
productsCount = c.Products.Count,
averagePrice = c.Products.Average(p => p.Price),
totalRevenue = c.Products.Sum(p => p.Price)
}).OrderByDescending(c => c.productsCount);
var json = JsonConvert.SerializeObject(categories, Formatting.Indented);
Console.WriteLine(json);
System.IO.File.WriteAllText(@"../../categories-by-products.json", json);
}
private static void CategoriesByProductsCount(ProductsShopContext db)
{
var categories = db.Categories
.Select(c => new
{
Category = c.Name,
ProductsCount = c.Products.Count,
AvaragePrice = c.Products.Average(p => p.Price),
TotalRevenue = c.Products.Sum(p => p.Price)
}).OrderByDescending(c => c.ProductsCount);
var json = JsonConvert.SerializeObject(categories, Formatting.Indented);
File.WriteAllText("../../../categories-by-products.json", json);
}
static void Main(string[] args)
{
var db = new ProductsShopContext();
//01. 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.
//ProductsInRangeAndWriteToJSON(db);
//02. 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.
//UserSoldProducts(db);
//3. 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).
CategoryByProductsToJSONFile(db);
}
private static void ProductsInRangeAndWriteToJSON(ProductsShopContext db)
{
var products = db.Products
.Where(p => p.Price > 500 && p.Price < 1000)
.Where(p => p.Buyer == null)
.Select(p => new
{
name = p.Name,
price = p.Price,
seller = (p.Seller.FirstName == null ? "" : p.Seller.FirstName) + " " + p.Seller.LastName
}).OrderByDescending(p => p.price).ToList();
var json = JsonConvert.SerializeObject(products, Formatting.Indented);
Console.WriteLine(json);
System.IO.File.WriteAllText(@"../../products-in-range.json", json);
}
private static void ProductsWithNoBuyerInRange(ProductsShopContext db, decimal minPrice, decimal maxPrice)
{
var products = db.Products
.Where(p =>
p.Price >= minPrice &&
p.Price <= maxPrice &&
p.Buyer != null
)
.Select(p => new
{
p.Name,
p.Price,
Seller = p.Seller.FirstName + " " + p.Seller.LastName
})
.OrderBy(p => p.Price);
var json = JsonConvert.SerializeObject(products, Formatting.Indented);
File.WriteAllText("../../../products-in-range.json", json);
}
private static void SuccessfullySoldProducts(ProductsShopContext db)
{
var sellers = db.Users
.Where(u => u.ProductsSold.Count >= 1)
.Select(u => new
{
u.FirstName,
u.LastName,
Product = u.ProductsSold.Select(p => new
{
p.Name,
p.Price,
BuyerFirstName = p.Buyer.FirstName,
BuyerLastName = p.Buyer.LastName
}).OrderBy(p => p.BuyerLastName).ThenBy(p => p.BuyerFirstName)
});
var json = JsonConvert.SerializeObject(sellers, Formatting.Indented);
File.WriteAllText("../../../users-sold-productions.json", json);
}
private static void UserSoldProducts(ProductsShopContext db)
{
Console.BufferHeight = 1300;
var users = db.Users
.Where(u => u.ProductsSold.Count > 0)
.Where(u => u.ProductsSold.Any(p => p.Buyer != null))
.Select(u => new
{
firstName = (u.FirstName == null ? "" : u.FirstName),
lastName = u.LastName,
soldProducts = u.ProductsSold.Where(b => b.Buyer != null)
.Select(p => new
{
name = p.Name,
price = p.Price,
buyerFirstName = (p.Buyer.FirstName == null ? "" : p.Buyer.FirstName),
buyerLastName = p.Buyer.LastName
})
}).OrderBy(u => u.lastName).ThenBy(u => u.firstName);
var json = JsonConvert.SerializeObject(users, Formatting.Indented);
Console.WriteLine(json);
System.IO.File.WriteAllText(@"../../users-sold-products.json", json);
}
public static void Seed(ProductsShopContext context)
{
if (!context.Users.Any())
{
IList <User> users;
IList <Product> products;
IList <Category> categories;
// import from json
//ImportDataAsJson(out users, out products, out categories);
// import from xml
ImportDataAsXml(out users, out products, out categories);
context.Users.AddRange(users);
context.SaveChanges();
int userId;
User user;
bool hasBuyer = false;
Random random = new Random();
foreach (var prod in products)
{
userId = random.Next(users.Count);
user = users[userId];
prod.Seller = user;
prod.SellerId = user.Id;
if (hasBuyer)
{
userId = random.Next(users.Count);
user = users[userId];
user.ProductsBought.Add(prod);
prod.Buyer = user;
prod.BuyerId = user.Id;
prod.Seller.ProductsSold.Add(prod);
context.Entry(user).State = EntityState.Modified;
}
hasBuyer = !hasBuyer;
}
context.Products.AddRange(products);
context.SaveChanges();
int categoryId;
Category category;
foreach (var prod in products)
{
categoryId = random.Next(categories.Count);
category = categories[categoryId];
var categoryProduct = new CategoryProduct
{
Product = prod,
ProductId = prod.Id,
Category = category,
CategoryId = categoryId
};
prod.CategoryProducts.Add(categoryProduct);
category.CategoryProducts.Add(categoryProduct);
context.Entry(prod).State = EntityState.Modified;
}
context.Categories.AddRange(categories);
context.SaveChanges();
}
}
private static void SeedUsers(ProductsShopContext context)
{
XmlDocument doc = new XmlDocument();
doc.Load(@"../../SeedData/users.xml");
var root = doc.DocumentElement;
foreach (XmlNode node in root.ChildNodes)
{
string firstName = null;
string lastName = node.Attributes["last-name"].Value;
if (node.Attributes["first-name"] != null)
{
firstName = node.Attributes["first-name"].Value;
}
User newUser = new User()
{
FirstName = firstName,
LastName = lastName,
};
if (node.Attributes["age"] != null)
{
newUser.Age = int.Parse(node.Attributes["age"].Value);
}
context.Users.Add(newUser);
context.SaveChanges();
}
}
static void Main(string[] args)
{
var db = new ProductsShopContext();
//01. 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.
var users = db.Users
.Where(u => u.ProductsSold.Count > 0)
.Select(u => new
{
FirstName = (u.FirstName == null ? null : u.FirstName),
LastName = u.LastName,
Age = (u.Age == null ? null : u.Age),
Products = u.ProductsSold.Select(p => new
{
name = p.Name,
price = p.Price
}),
NumbOfSoldProducts = u.ProductsSold.Count
}).OrderByDescending(u => u.NumbOfSoldProducts).ThenBy(u => u.LastName);
var usersXML = new XElement("users",
new XAttribute("count", users.Count()));
foreach (var user in users)
{
var userInfoXMl = new XElement("user");
string firstName = user.FirstName;
string lastName = user.LastName;
string age = user.Age.ToString() =="" ? null : user.Age.ToString();
if (firstName != null)
{
userInfoXMl.Add(new XAttribute("first-name",firstName));
}
userInfoXMl.Add(new XAttribute("last-name",lastName));
if (age != null)
{
userInfoXMl.Add( new XAttribute("age",age));
}
var products = user.Products;
var productsSoldXML = new XElement("sold-products",new XAttribute("count",products.Count()));
userInfoXMl.Add(productsSoldXML);
foreach (var product in products)
{
var productXMl = new XElement("product");
productXMl.Add(new XAttribute("name", product.name));
productXMl.Add(new XAttribute("price", product.price));
productsSoldXML.Add(new XElement(productXMl));
}
usersXML.Add(userInfoXMl);
}
Console.WriteLine(usersXML);
usersXML.Save("../../users.xml");
}
//private const decimal MinPrice = 500;
//private const decimal MaxPrice = 1000;
static void Main(string[] args)
{
var context = new ProductsShopContext();
//var products = context.Products
// .Where(p => MinPrice <= p.Price && p.Price <= MaxPrice && p.BuyerId == null)
// .Select(p => new
// {
// name = p.Name,
// price = p.Price,
// seller = p.Seller.FirstName + " " + p.Seller.LastName
// });
//string json = JsonConvert.SerializeObject(products, Formatting.Indented);
//File.WriteAllText("../../products-in-range.json", json);
//var users = context.Users
// .Where(u => u.SoldProducts.Count > 0)
// .OrderBy(u => u.LastName)
// .ThenBy(u => u.FirstName)
// .Select(u => new
// {
// firstName = u.FirstName,
// lastName = u.LastName,
// soldProducts = u.SoldProducts
// .Where(sp => sp.BuyerId != null)
// .Select(sp => new
// {
// name = sp.Name,
// price = sp.Price,
// buyerFirstName = sp.Buyer.FirstName,
// buyerLastName = sp.Buyer.LastName
// })
// });
//string json = JsonConvert.SerializeObject(users, Formatting.Indented);
//File.WriteAllText("../../users-sold-products.json", json);
//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)
// });
//string json = JsonConvert.SerializeObject(categories, Formatting.Indented);
//File.WriteAllText("../../categories-by-products.json", json);
var users = context.Users
.Where(u => u.SoldProducts.Count > 0)
.OrderByDescending(u => u.SoldProducts.Count)
.ThenBy(u => u.LastName)
.Select(u => new
{
FirstName = u.FirstName == null ? null : u.FirstName,
u.LastName,
Age = u.Age == null ? null : u.Age,
SoldProducts = u.SoldProducts
.Select(sp => new
{
sp.Name,
sp.Price
})
});
var settings = new XmlWriterSettings()
{
Indent = true,
NewLineChars = "\n"
};
using (XmlWriter writer = XmlWriter.Create("../../users-and-products.xml", settings))
{
writer.WriteStartDocument();
writer.WriteStartElement("users");
writer.WriteAttributeString("count", users.Count().ToString());
foreach (var user in users)
{
writer.WriteStartElement("user");
if (user.FirstName != null && user.Age != null)
{
writer.WriteAttributeString("first-name", user.FirstName);
writer.WriteAttributeString("last-name", user.LastName);
writer.WriteAttributeString("age", user.Age.ToString());
}
foreach (var soldProduct in user.SoldProducts)
{
writer.WriteStartElement("product");
writer.WriteAttributeString("name", soldProduct.Name);
writer.WriteAttributeString("price", soldProduct.Price.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
}
}
static void Main()
{
//set database
Database.SetInitializer(new MigrateDatabaseToLatestVersion<ProductsShopContext, Configuration>());
var context = new ProductsShopContext();
var count = context.Products.Count();
Console.WriteLine(count);
//Problem 3. Query and Export Data
//Write the below described queries and export the returned data to the specified format.
//Make sure that Entity Framework generates only a single query for each task.
//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
.Where(p => p.Price >= 500 && p.Price <= 1000 && p.Buyer == null)
.OrderBy(p => p.Price)
.Select(p => new
{
p.Name,
p.Price,
Seller = p.Seller.FirstName + " " + p.Seller.LastName
});
var jsonProductsInRange = JsonConvert.SerializeObject(productsInRange, Newtonsoft.Json.Formatting.Indented);
var path = "../../../" + "ProductsInRange" + ".json";
File.WriteAllText(path, jsonProductsInRange);
Console.WriteLine(jsonProductsInRange);
//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 usersWithSoldProducts = context.Users
.Where(u => u.SoldProducts.Any())
.OrderBy(u => u.FirstName)
.ThenBy(u => u.LastName)
.Select(u => new
{
FirstName = u.FirstName ?? "N/A",
LastName = u.LastName,
SoldProduct = u.SoldProducts.Select(p => new
{
Productname = p.Name,
Price = p.Price,
Buyer = p.Buyer.FirstName + " " + p.Buyer.LastName
})
});
var jsonUsersWithSoldProducts = JsonConvert.SerializeObject(usersWithSoldProducts, Newtonsoft.Json.Formatting.Indented);
var path2 = "../../../" + "UsersWithSoldProducts" + ".json";
File.WriteAllText(path2, jsonUsersWithSoldProducts);
Console.WriteLine(jsonUsersWithSoldProducts);
//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 categoriesByProductsCount = context.Categories
.OrderBy(c => c.Products.Count)
.Select(c => new
{
c.Name,
NumberOfProducts = c.Products.Count,
AveragePrice = c.Products.Average(p => p.Price),
TotalSum = c.Products.Sum(p => p.Price)
});
var jsonCategoriesByProductsCount = JsonConvert.SerializeObject(categoriesByProductsCount, Newtonsoft.Json.Formatting.Indented);
var path3 = "../../../" + "CategoriesByProductsCount" + ".json";
File.WriteAllText(path2, jsonCategoriesByProductsCount);
Console.WriteLine(jsonCategoriesByProductsCount);
//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 usersWithProducts = context.Users
.Where(u => u.SoldProducts.Count > 0)
.OrderByDescending(u => u.SoldProducts.Count)
.ThenBy(u => u.LastName)
.Select(u => new
{
Count = u.SoldProducts.Count,
FirstName = u.FirstName ?? "N/A",
LastName = u.LastName,
Age = u.Age,
Products = u.SoldProducts.Select(p => new
{
ProductName = p.Name,
Price = p.Price
})
});
var encoding = Encoding.GetEncoding("utf-8");
using (var writer = new XmlTextWriter("../../../usersWithProducts.xml", encoding))
{
writer.Formatting = System.Xml.Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("users");
foreach (var user in usersWithProducts)
{
writer.WriteStartElement("user");
writer.WriteAttributeString("first-name", user.FirstName);
writer.WriteAttributeString("last-name", user.LastName);
writer.WriteAttributeString("age", user.Age.ToString());
writer.WriteStartElement("sold-products");
foreach (var product in user.Products)
{
writer.WriteStartElement("product");
writer.WriteAttributeString("name", product.ProductName);
writer.WriteAttributeString("price", product.Price.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Close();
}
Console.WriteLine("Xml document has been created.");
}