public static string GetUsersWithProducts(ProductShopContext context)
{
var users = context
.Users
.Where(u => u.ProductsSold.Any())
.OrderByDescending(u => u.ProductsSold.Count())
.Select(u => new ExportUserWithProductDto()
{
FirstName = u.FirstName,
LastName = u.LastName,
Age = u.Age,
SoldProducts = new SoldProductsCountDto()
{
Count = u.ProductsSold.Count(),
Products = u.ProductsSold.Select(p => new ExportProductDto()
{
Name = p.Name,
Price = p.Price
})
.OrderByDescending(p => p.Price)
.ToArray()
}
})
.Take(10)
.ToArray();
var result = new UsersAndProductsDto
{
Count = context.Users.Count(p => p.ProductsSold.Any()),
Users = users
};
var sb = new StringBuilder();
var serializer = new XmlSerializer(typeof(UsersAndProductsDto), new XmlRootAttribute("Users"));
var namespaces = new XmlSerializerNamespaces();
namespaces.Add("", "");
using (var writer = new StringWriter(sb))
{
serializer.Serialize(writer, result, namespaces);
}
return(sb.ToString().Trim());
}
public static string GetUsersWithProducts(ProductShopContext context)
{
int usersCount = context.Users
.Where(u => u.ProductsSold.Count > 0).Count();
var users = context.Users
.Where(u => u.ProductsSold.Any())
.OrderByDescending(p => p.ProductsSold.Count())
.Select(u => new UserProductsDto
{
FirstName = u.FirstName,
LastName = u.LastName,
Age = u.Age,
Products = new ProductsDto
{
Count = u.ProductsSold.Count(),
Products = u.ProductsSold
.Select(p => new SoldProductDto
{
Name = p.Name,
Price = p.Price
})
.OrderByDescending(p => p.Price)
.ToArray()
}
})
.Take(10)
.ToArray();
var result = new UsersAndProductsDto
{
Count = usersCount,
Users = users
};
StringBuilder sb = new StringBuilder();
XmlSerializer ser = new XmlSerializer(typeof(UsersAndProductsDto), new XmlRootAttribute("Users"));
var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
ser.Serialize(new StringWriter(sb), result, namespaces);
return(sb.ToString().TrimEnd());
}
//08-Export Users and Products
public static string GetUsersWithProducts(ProductShopContext context)
{
var users = context.Users
.Where(u => u.ProductsSold.Any(p => p.Buyer != null))
.OrderByDescending(u => u.ProductsSold.Count(ps => ps.Buyer != null))
.Select(u => new UserWithProductsDto
{
FirstName = u.FirstName,
LastName = u.LastName,
Age = u.Age,
SoldProducts = new SoldProductsToUserDto
{
Count = u.ProductsSold.Count(p => p.Buyer != null),
Products = u.ProductsSold
.Where(p => p.Buyer != null)
.Select(p => new SoldProductsDto
{
Name = p.Name,
Price = p.Price
})
.ToList()
}
})
.ToList();
var result = new UsersAndProductsDto
{
UsersCount = users.Count,
Users = users
};
var json = JsonConvert.SerializeObject(result,
Formatting.Indented,
new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore
});
return(json);
}
public void ExportToXml()
{
// Query 1
ProductsInRangeDto[] productsInRange = this.GetProductsInRangeXml();
XDocument productsXml = new XDocument(new XElement("products"));
foreach (var p in productsInRange)
{
productsXml.Root.Add(new XElement("product", new XAttribute("name", p.Name), new XAttribute("price", p.Price), new XAttribute("buyer", p.Seller)));
}
File.WriteAllText(@"../Query 1 ProductsInRangeXml.xml", productsXml.ToString());
Process.Start("notepad.exe", @"../Query 1 ProductsInRangeXml.xml");
// Query 2
SoldProductsXmlDto[] soldProducts = this.GetSoldProductsXML();
XDocument soldProductsXml = new XDocument(new XElement("users"));
foreach (var p in soldProducts)
{
var sold = new XElement("sold-products");
foreach (var pr in p.SoldProducts)
{
sold.Add(new XElement("product", new XElement("name", pr.Name), new XElement("price", pr.Price)));
}
soldProductsXml.Root.Add(new XElement("user", p.FirstName != null ? new XAttribute("first-name", p.FirstName) : null, new XAttribute("last-name", p.LastName), sold));
}
File.WriteAllText(@"../Query 2 SoldProductsXml.xml", soldProductsXml.ToString());
Process.Start("notepad.exe", @"../Query 2 SoldProductsXml.xml");
// Query 3
CategoriesByProductsCountXml[] catByProductsCount = this.GetCategoriesByProductsCountXml();
XDocument catByProductsCountXml = new XDocument(new XElement("categories"));
foreach (var c in catByProductsCount)
{
catByProductsCountXml.Root.Add(new XElement("category", new XAttribute("name", c.Category), new XElement("products-count", c.ProductsCount), new XElement("average-prive", c.AveragePrice), new XElement("total-revenue", c.TotalRevenue)));
}
File.WriteAllText(@"../Query 3 CategoriesByProductsCountXml.xml", catByProductsCountXml.ToString());
Process.Start("notepad.exe", @"../Query 3 CategoriesByProductsCountXml.xml");
// Query 4
UsersAndProductsDto usersAndProducts = this.GetUsersAndProductsJsonXml();
XDocument usersAndProductsXml = new XDocument(new XElement("users", new XAttribute("count", usersAndProducts.UsersCount)));
foreach (var u in usersAndProducts.Users)
{
XAttribute firstName = u.FirstName != null ? new XAttribute("first-name", u.FirstName) : null;
XAttribute lastName = new XAttribute("last-name", u.LastName);
XAttribute age = u.Age != null ? new XAttribute("age", u.Age) : null;
XElement user = new XElement("user", firstName, lastName, age);
XElement productsSold = new XElement("sold-products", new XAttribute("count", u.SoldProducts.Count));
foreach (var pr in u.SoldProducts.Products)
{
XElement currProd = new XElement("product", new XAttribute("name", pr.Name), new XAttribute("price", pr.Price));
productsSold.Add(currProd);
}
user.Add(productsSold);
usersAndProductsXml.Root.Add(user);
}
File.WriteAllText(@"../Query 4 UsersAndProducts.xml", usersAndProductsXml.ToString());
Process.Start("notepad.exe", @"../Query 4 UsersAndProducts.xml");
}
