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