public static string ImportCategoryProducts(ProductShopContext context, string inputJson) { //todo its Broken must be refactoring!!! var validCategoriesId = context.Categories.Select(x => x.Id).ToHashSet(); var validProductsId = context.Products.Select(x => x.Id).ToHashSet(); var categoryProducts = JsonConvert.DeserializeObject <CategoryProduct[]>(inputJson); var validCategoriesProducts = new List <CategoryProduct>(); foreach (var categoryProduct in categoryProducts) { var isValid = validCategoriesId.Contains(categoryProduct.CategoryId) && validProductsId.Contains(categoryProduct.ProductId); if (isValid) { validCategoriesProducts.Add(categoryProduct); } } context.CategoryProducts.AddRange(validCategoriesProducts); context.SaveChanges(); return(string.Format(reportMessage, validCategoriesProducts.Count)); }
//Problem02 public static string ImportProducts(ProductShopContext context, string inputXml) { var usersResult = XMLConverter.Deserializer <ImportProductDto>(inputXml, "Products"); List <Product> products = new List <Product>(); foreach (var p in usersResult) { Product product = new Product() { Name = p.Name, Price = (decimal)p.Price, BuyerId = p.BuyerId, SellerId = p.SellerId }; products.Add(product); } context.AddRange(products); context.SaveChanges(); return($"Successfully imported {products.Count}"); }
public static string GetUsersWithProducts(ProductShopContext context) { var users = new { usersCount = context.Users.Count(u => u.ProductsSold.Count > 0 && u.ProductsSold.Any(x => x.BuyerId != null)), users = context.Users .Where(u => u.ProductsSold.Count > 0 && u.ProductsSold.Any(x => x.Buyer != null)) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, soldProducts = new { count = u.ProductsSold.Count(x => x.Buyer != null), products = u.ProductsSold .Where(x => x.Buyer != null) .Select(p => new { name = p.Name, price = p.Price }).ToList() } }) .OrderByDescending(x => x.soldProducts.count) .ToList() }; string result = JsonConvert.SerializeObject(users, new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore, Formatting = Formatting.Indented, }); return(result); }
public static string GetCategoriesByProductsCount(ProductShopContext context) { StringBuilder result = new StringBuilder(); var categories = context .Categories .ProjectTo <CategoryByProductDTO>() .OrderByDescending(c => c.ProductsCount) .ThenBy(c => c.TotalRevenue) .ToArray(); var xmlSerializer = new XmlSerializer(typeof(CategoryByProductDTO[]), new XmlRootAttribute("Categories")); var namespaces = new XmlSerializerNamespaces(); namespaces.Add(String.Empty, String.Empty); using (var categoriesWriter = new StringWriter(result)) { xmlSerializer.Serialize(categoriesWriter, categories, namespaces); } return(result.ToString().TrimEnd()); }
public static string GetUsersWithProducts(ProductShopContext context) { var users = context.Users .Where(x => x.ProductsSold.Any(b => b.Buyer != null)) .Select(x => new { lastName = x.LastName, age = x.Age, soldProducts = new { count = x.ProductsSold .Count(b => b.Buyer != null), products = x.ProductsSold .Where(b => b.Buyer != null) .Select(ps => new { name = ps.Name, price = ps.Price }).ToArray() } }).OrderByDescending(x => x.soldProducts.count) .ToArray(); var resultObj = new { usersCount = users.Length, users = users }; string json = JsonConvert.SerializeObject(resultObj, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }); return(json); }
//06. Export Sold Products public static string GetSoldProducts(ProductShopContext context) { InitializeMapper(); var namespaces = new XmlSerializerNamespaces(); namespaces.Add("", ""); StringBuilder sb = new StringBuilder(); var users = context.Users .Where(x => x.ProductsSold.Count > 0) .ProjectTo <UserSoldProductOutputModel>(mapper.ConfigurationProvider) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .Take(5) .ToArray(); var serializer = new XmlSerializer(typeof(UserSoldProductOutputModel[]), new XmlRootAttribute("Users")); serializer.Serialize(new StringWriter(sb), users, namespaces); return(sb.ToString()); }
public static string ImportUsers(ProductShopContext context, string inputXml) { var xmlSerializer = new XmlSerializer(typeof(UserInputModel[]), new XmlRootAttribute("Users")); var usersDtos = xmlSerializer.Deserialize(new StringReader(inputXml)); //InizializedAutomapper(); var usersMap = Mapper.Map <User[]>(usersDtos); //var userTest = usersDtos // .Select(x => new User // { // FirstName = x.FirstName, // LastName = x.LastName, // Age = x.Age // }) // .ToArray(); context.Users.AddRange(usersMap); context.SaveChanges(); return($"Successfully imported {usersMap.Length}"); }
// Task - Export Users with Products public static string GetUsersWithProducts(ProductShopContext context) { var users = context .Users .Where(x => x.ProductsSold.Any(p => p.Buyer != null)) .OrderByDescending(x => x.ProductsSold.Count(p => p.Buyer != null)) .Select(x => new { lastName = x.LastName, age = x.Age, soldProducts = new { count = x.ProductsSold .Where(p => p.Buyer != null).Count(), products = x.ProductsSold .Where(p => p.Buyer != null) .Select(p => new { name = p.Name, price = p.Price }) .ToArray() } }) .ToArray(); var result = new { usersCount = users.Length, users = users }; string json = JsonConvert.SerializeObject(result, Formatting.Indented); return(json); }
public static string GetCategoriesByProductsCount(ProductShopContext context) { var result = context.Categories .Select(x => new { Category = x.Name, ProductsCount = x.CategoryProducts.Count(), AveragePrice = x.CategoryProducts.Average(p => p.Product.Price).ToString("F2"), TotalRevenue = x.CategoryProducts.Sum(p => p.Product.Price).ToString("F2") }) .OrderByDescending(x => x.ProductsCount) .ToArray(); var json = JsonConvert.SerializeObject(result, new JsonSerializerSettings() { ContractResolver = new DefaultContractResolver() { NamingStrategy = new CamelCaseNamingStrategy() }, Formatting = Formatting.Indented }); return(json); }
public static string ImportCategories(ProductShopContext context, string inputXml) { XmlSerializer seriazlizer = new XmlSerializer(typeof(ImportCategoriesDto[]), new XmlRootAttribute("Categories")); ImportCategoriesDto[] serializedCategories; using (var reader = new StringReader(inputXml)) { serializedCategories = (ImportCategoriesDto[])seriazlizer.Deserialize(reader); } var categories = serializedCategories .Where(x => x.Name != null) .Select(x => new Category { Name = x.Name }) .ToArray(); context.Categories.AddRange(categories); context.SaveChanges(); return($"Successfully imported {categories.Length}"); }
//06. Export Sold Products public static string GetSoldProducts(ProductShopContext context) { var usersDTO = context.Users .Where(x => x.ProductsSold.Any()) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .Take(5) .Select(x => new ExportUsersDTO { FirstName = x.FirstName, LastName = x.LastName, SoldProducts = x.ProductsSold.Select(y => new ExportProductsDTO { Name = y.Name, Price = y.Price }) .ToArray() }) .ToArray(); var rootAttributeName = "Users"; return(XMLConverter.Serialize(usersDTO, rootAttributeName)); }
//01. Import Users public static string ImportUsers(ProductShopContext context, string inputXml) { var serializer = new XmlSerializer(typeof(List <UserDto>), new XmlRootAttribute("Users")); List <UserDto> userDtos; using (var reader = new StringReader(inputXml)) { userDtos = (List <UserDto>)serializer.Deserialize(reader); } var users = new List <User>(); userDtos.ForEach(ud => users.Add(new User { FirstName = ud.FirstName, LastName = ud.LastName, Age = ud.Age, })); context.Users.AddRange(users); var insertedUserRecordsCount = context.SaveChanges(); return($"Successfully imported {insertedUserRecordsCount}"); }
//8 public static string GetUsersWithProducts(ProductShopContext context) { var users = context .Users .Where(u => u.ProductsSold.Any(p => p.Buyer != null)) .OrderByDescending(u => u.ProductsSold.Count(p => p.Buyer != null)) .Select(u => new UserWithProductDto { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age, SoldProducts = new SoldProductsForUserWith { Count = u.ProductsSold.Count(p => p.Buyer != null), Products = u.ProductsSold .Where(p => p.Buyer != null) .Select(p => new ProductForSoldProducts { Name = p.Name, Price = p.Price }) .ToList() } }) .ToList(); var allUsers = new UsersDto { UsersCount = users.Count, Users = users }; var usersJason = JsonConvert.SerializeObject(allUsers, Formatting.Indented); return(usersJason); }
//Problem 05 public static string GetProductsInRange(ProductShopContext context) { var xmlSerializer = new XmlSerializer(typeof(ExportProductsInRange[]), new XmlRootAttribute("Products")); var products = context.Products .Where(p => p.Price >= 500 && p.Price <= 1000) .OrderBy(p => p.Price) .ProjectTo <ExportProductsInRange>() .Take(10) .ToArray(); StringBuilder sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(); namespaces.Add(string.Empty, string.Empty); using (var writer = new StringWriter(sb)) { xmlSerializer.Serialize(writer, products, namespaces); } return(sb.ToString().TrimEnd()); }
//07-Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { var categories = context.Categories .Select(c => new GetCategoriesDto { Name = c.Name, Count = c.CategoryProducts.Count(), AveragePrice = c.CategoryProducts.Average(cp => cp.Product.Price), TotalRevenue = c.CategoryProducts.Sum(p => p.Product.Price) }) .OrderByDescending(c => c.Count) .ThenBy(c => c.TotalRevenue) .ToArray(); var serializer = new XmlSerializer(typeof(GetCategoriesDto[]), new XmlRootAttribute("Categories")); StringBuilder sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }); serializer.Serialize(new StringWriter(sb), categories, namespaces); return(sb.ToString().TrimEnd()); }
//01. Import Users public static string ImportUsers(ProductShopContext context, string inputXml) { const string roodElement = "Users"; var usersResult = XMLConverter.Deserializer <ImportUserDto>(inputXml, roodElement); /* Първи начин * List<User> users = new List<User>(); * * foreach (var importUserDtio in usersResult) * { * var user = new User * { * FirstName = importUserDtio.FirstName, * LastName = importUserDtio.LastName, * Age = importUserDtio.Age * }; * * users.Add(user); * } */ //Втори начин var users = usersResult .Select(u => new User { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age }) .ToArray(); context.Users.AddRange(users); context.SaveChanges(); return($"Successfully imported {users.Length}"); }
//07. Export Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { StringBuilder sb = new StringBuilder(); var categories = context .Categories .Select(c => new ExportCategoryDto { Name = c.Name, CountOfProducts = c.CategoryProducts.Count(), AverageProductsPrice = c.CategoryProducts.Average(cp => cp.Product.Price), TotalRevenue = c.CategoryProducts.Sum(p => p.Product.Price) }) .OrderByDescending(c => c.CountOfProducts) .ThenBy(c => c.TotalRevenue) .ToArray(); var XmlSerializer = new XmlSerializer(typeof(ExportCategoryDto[]), new XmlRootAttribute("Categories")); var namespaces = new XmlSerializerNamespaces(); namespaces.Add("", ""); using (var writer = new StringWriter(sb)) { XmlSerializer.Serialize(writer, categories, namespaces); } return(sb.ToString().TrimEnd()); }
public static string ImportUsers(ProductShopContext context, string inputXml) { XmlSerializer serializer = new XmlSerializer(typeof(ImportUserDto[]) , new XmlRootAttribute("Users")); var resultUsers = new List <User>(); using (StringReader stringReader = new StringReader(inputXml)) { var users = (ImportUserDto[])serializer.Deserialize(stringReader); foreach (var userDto in users) { var user = Mapper.Map <User>(userDto); resultUsers.Add(user); } } context.Users.AddRange(resultUsers); context.SaveChanges(); return($"Successfully Imported {resultUsers.Count}"); }
public static string ImportUsers(ProductShopContext context, string inputXml) { const string roodElement = "Users"; var usersResult = XmlConverter.Deserializer <ImportUserDto>(inputXml, roodElement); List <User> users = new List <User>(); foreach (var importUserDtio in usersResult) { var user = new User { FirstName = importUserDtio.FirstName, LastName = importUserDtio.LastName, Age = importUserDtio.Age }; users.Add(user); } context.Users.AddRange(users); context.SaveChanges(); return($"Successfully imported {users.Count}"); }
//3. Import Categories public static string ImportCategories(ProductShopContext context, string inputXml) { /*Import the categories from the provided file categories.xml. * Some of the names will be null, so you don’t have to add them in the database. * Just skip the record and continue.*/ Mapper.Initialize(cfg => { cfg.AddProfile <ProductShopProfile>(); }); var serializer = new XmlSerializer(typeof(List <ImportCategoriesDto>), new XmlRootAttribute("Categories")); var categoriesDtos = (List <ImportCategoriesDto>)serializer.Deserialize(new StringReader(inputXml)); List <Category> categories = new List <Category>(); // judge no like map //var categories = Mapper.Map<List<Category>>(categoriesDtos) // .Where(c => c.Name != null) // .ToList(); foreach (var categoriesDto in categoriesDtos) { Category category = new Category() { Name = categoriesDto.Name }; if (category.Name != null) // in the xml none of them are null but ok { categories.Add(category); } } context.Categories.AddRange(categories); context.SaveChanges(); return($"Successfully imported {categories.Count}"); }
public static string GetUsersWithProducts(ProductShopContext db) { var users = db.Users .Where(x => x.ProductsSold.Any(p => p.Buyer != null)) .Select(u => new UserProductsDTO { LastName = u.LastName, Age = u.Age, SoldProducts = u.ProductsSold.Where(x => x.Buyer != null) .Select(cp => new SoldProductsDTO { Count = cp.CategoryProducts.Count, Products = cp.CategoryProducts.Select(p => new ProductNamePriceDTO { Name = p.Product.Name, Price = p.Product.Price }).ToList() }).ToList() }).ToList() .OrderByDescending(x => x.SoldProducts.Count) .ToList(); var usersProducts = new UsersCountProducts() { Users = users }; string json = JsonConvert.SerializeObject(usersProducts, new JsonSerializerSettings() { Formatting = Formatting.Indented, ContractResolver = new CamelCasePropertyNamesContractResolver(), NullValueHandling = NullValueHandling.Ignore }); return(json); }
public static string GetSoldProducts(ProductShopContext context) { var result = context .Users .Where(x => x.ProductsSold.Any(ps => ps.Buyer != null)) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .Select(x => new { FirstName = x.FirstName, LastName = x.LastName, SoldProducts = x.ProductsSold .Where(y => y.Buyer != null) .Select(y => new { Name = y.Name, Price = y.Price, BuyerFirstName = y.Buyer.FirstName, BuyerLastName = y.Buyer.LastName }).ToList() }).ToList(); DefaultContractResolver contractResolver = new DefaultContractResolver() { NamingStrategy = new CamelCaseNamingStrategy() }; var json = JsonConvert.SerializeObject(result, new JsonSerializerSettings { Formatting = Formatting.Indented, ContractResolver = contractResolver }); return(json); }
public static string GetSoldProducts(ProductShopContext context) { var users = context.Users .Where(x => x.ProductsSold.Any(b => b.Buyer != null)) .OrderBy(x => x.LastName).ThenBy(x => x.FirstName) .Select(x => new { firstName = x.FirstName, lastName = x.LastName, soldProducts = x.ProductsSold .Where(p => p.Buyer != null) .Select(p => new { name = p.Name, price = p.Price, buyerFirstName = p.Buyer.FirstName, buyerLastName = p.Buyer.LastName }).ToList() }).ToList(); string json = JsonConvert.SerializeObject(users, Formatting.Indented); return(json); }
private static void SuccessfullySoldProducts() { using (var context = new ProductShopContext()) { var users = context.Users .Where(u => u.ProductsSold.Count > 1) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, SoldProducts = u.ProductsSold .Select(p => new { Name = p.Name, Price = p.Price, BuyerFirstName = p.Buyer.FirstName, BuyerLastName = p.Buyer.LastName }) }) .OrderBy(o => o.LastName) .ThenBy(o => o.FirstName) .ToList(); var settings = new JsonSerializerSettings { Formatting = Formatting.Indented, ContractResolver = new CamelCasePropertyNamesContractResolver() }; var usersJson = JsonConvert.SerializeObject(users, settings); File.WriteAllText(@"..\..\Exports\users-sold-products.json", usersJson); } }
// Export Problems public static string GetProductsInRange(ProductShopContext context) { JsonSerializerSettings settings = new JsonSerializerSettings() { Formatting = Formatting.Indented, ContractResolver = new CamelCasePropertyNamesContractResolver() }; var products = context.Products .Where(p => 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(); var productAsJson = JsonConvert.SerializeObject(products, settings); return(productAsJson); }
//Query 7. Categories By Products Count public static string GetCategoriesByProductsCount(ProductShopContext context) { var categories = context .Categories .Select(x => new ExportCategoriesByCountDto { Name = x.Name, ProductsCount = x.CategoryProducts.Count, AveragePrice = x.CategoryProducts.Select(a => a.Product.Price).Average(), TotalRevenue = x.CategoryProducts.Select(а => а.Product.Price).Sum() }) .OrderByDescending(x => x.ProductsCount) .ThenBy(x => x.TotalRevenue) .ToArray(); var sb = new StringBuilder(); var serializer = new XmlSerializer(typeof(ExportCategoriesByCountDto[]), new XmlRootAttribute("Categories")); var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }); serializer.Serialize(new StringWriter(sb), categories, namespaces); return(sb.ToString().TrimEnd()); }
public static string ImportCategoryProducts(ProductShopContext context, string inputXml) { const string rootElement = "CategoryProducts"; var categoryProductResult = XmlConverter.Deserializer <ImportCategoryProductDTO>(inputXml, rootElement); //.where() clause is => //=> if category id or product id doens't exist var categoryProducts = categoryProductResult .Where(x => context.Categories.Any(c => c.Id == x.CategoryId) && context.Products.Any(p => p.Id == x.ProductId)) .Select(cp => new CategoryProduct { CategoryId = cp.CategoryId, ProductId = cp.ProductId }).ToList(); context.CategoryProducts.AddRange(categoryProducts); context.SaveChanges(); return($"Successfully imported {categoryProducts.Count}"); }
//05-Export Products In Range public static string GetProductsInRange(ProductShopContext context) { var products = context.Products .Select(p => new ProductInRangeDto { Name = p.Name, Price = p.Price, Buyer = $"{p.Buyer.FirstName} {p.Buyer.LastName}" }) .Where(p => p.Price >= 500 && p.Price <= 1000) .OrderBy(p => p.Price) .Take(10) .ToArray(); var serializer = new XmlSerializer(typeof(ProductInRangeDto[]), new XmlRootAttribute("Products")); StringBuilder sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }); serializer.Serialize(new StringWriter(sb), products, namespaces); return(sb.ToString().TrimEnd()); }
public static string ImportCategories(ProductShopContext context, string inputXml) { InitializeMapper(); var serializer = new XmlSerializer(typeof(CategoriesInputViewModel[]), new XmlRootAttribute("Categories")); var textReader = new StringReader(inputXml); var categoriesInputModels = (CategoriesInputViewModel[])serializer.Deserialize(textReader); var categoriesToImport = categoriesInputModels .Where(x => x.Name != null) .AsQueryable() .ProjectTo <Category>(mapper.ConfigurationProvider) .ToArray(); ; context.Categories.AddRange(categoriesToImport); context.SaveChanges(); return($"Successfully imported {categoriesToImport.Length}"); }
public static void Main(string[] args) { Mapper.Initialize(cfg => { cfg.AddProfile <ProductShopProfile>(); }); //string userPath = // @"..\..\..\Datasets\users.xml"; //string productsPath = // @"..\..\..\Datasets\products.xml"; //string categoriesPath = // @"..\..\..\Datasets\categories.xml"; //string categoriesProductsPath = // @"..\..\..\Datasets\categories-products.xml"; using (ProductShopContext db = new ProductShopContext()) { Console.WriteLine(GetUsersWithProducts(db)); } }