static void MostRecentBooks(BookSystemDbContext context) { var books = context.Categories .Select(c => new { Name = c.Name, TotalBooks = c.Books.Count, Books = c.Books.OrderByDescending(b => b.ReleaseDate) .ThenBy(b => b.Title) .Take(3) .Select(b => new { Title = b.Title, ReleaseDate = b.ReleaseDate }) }) .Where(c => c.TotalBooks > 35) .OrderByDescending(c => c.TotalBooks) .ToList(); foreach (var category in books) { Console.WriteLine($"--{category.Name}: {category.TotalBooks} books"); foreach (var book in category.Books) { Console.WriteLine($"{book.Title} ({book.ReleaseDate.Year})"); } } }
private void SeedCategories(BookSystemDbContext context) { int booksCount = context.Books.Local.Count; string[] categories = File.ReadAllLines($@"{AppDomain.CurrentDomain.BaseDirectory}\Import\categories.csv"); for (int i = 1; i < categories.Length; i++) { string[] data = categories[i] .Split(',') .Select(arg => arg.Replace("\"", string.Empty)) .ToArray(); string categoryName = "Random Category" + i; Category category = new Category() { Name = categoryName }; int bookIndex = (i * 30) % booksCount; for (int j = 0; j < bookIndex; j++) { Book book = context.Books.Local[j]; category.Books.Add(book); } context.Categories.AddOrUpdate(c => c.Name, category); } }
private static void RemoveBooks(BookSystemDbContext context) { var booksForDelete = context.Books.Where(b => b.Copies < 4200).ToList(); int totalNumberOfBooksDeleted = context.Books.Where(b => b.Copies < 4200).Delete(); context.SaveChanges(); Console.WriteLine($"{totalNumberOfBooksDeleted} books were deleted"); }
static void Main() { Database.SetInitializer <BookSystemDbContext>(new CreateDatabaseIfNotExists <BookSystemDbContext>()); var context = new BookSystemDbContext(); SetupBooksRelatedBooks(context); PrintBooksWithRelatedBooks(context); }
private static void SetupBooksRelatedBooks(BookSystemDbContext context) { var books = context.Books.Take(3).ToList(); books[0].RelatedBooks.Add(books[1]); books[1].RelatedBooks.Add(books[1]); books[0].RelatedBooks.Add(books[1]); books[2].RelatedBooks.Add(books[1]); context.SaveChanges(); }
static void GoldenBooks(BookSystemDbContext context) { context.Books .Where(c => c.Copies < 5000 && c.Edition == "Gold") .OrderBy(i => i.Id) .Select(b => new { Title = b.Title }) .ToList() .ForEach(c => Console.WriteLine(c.Title)); }
static void BooksByPrice(BookSystemDbContext context) { context.Books .Where(b => b.Price > 40 || b.Price < 5) .Select(b => new { Title = b.Title, Price = b.Price }) .ToList() .ForEach(b => Console.WriteLine($"{b.Title} - ${b.Price}")); }
private static void PrintBooksWithRelatedBooks(BookSystemDbContext context) { var books = context.Books.Include(b => b.RelatedBooks).Take(3).ToList(); foreach (var book in books) { System.Console.WriteLine($"--{book.Title}"); foreach (var relatedBook in book.RelatedBooks) { System.Console.WriteLine(relatedBook.Title); } } }
static void NotReleasedBooks(BookSystemDbContext context) { int inputDate = int.Parse(Console.ReadLine()); context.Books .Where(b => b.ReleaseDate.Year != inputDate) .Select(b => new { Title = b.Title }) .ToList() .ForEach(b => Console.WriteLine(b.Title)); }
static void SearchBook(BookSystemDbContext context) { string input = Console.ReadLine().ToLower(); var books = context.Books .Where(b => b.Title.ToLower().Contains(input)) .Select(b => new { b.Title }) .ToList(); books.ForEach(b => Console.WriteLine(b.Title)); }
static void CountBooks(BookSystemDbContext context) { int titleLenght = int.Parse(Console.ReadLine()); var books = context.Books .Where(b => b.Title.Length > titleLenght) .Select(b => new { b.Title }) .ToList().Count(); Console.WriteLine(books); }
static void AgeRestriction(BookSystemDbContext context) { string input = Console.ReadLine(); var listbooks = context.Books.ToList(); foreach (var book in listbooks) { if (book.AgeRestriction.ToLower() == input.ToLower()) { Console.WriteLine(book.Title); } } }
private static void IncreaseBookCopies(BookSystemDbContext context) { int totalBooksUpdated = context.Books .Where(b => b.ReleaseDate > new DateTime(2013, 06, 06)) .Update(b => new Book { Copies = b.Copies + 44 }); context.SaveChanges(); int totalCopiesAdded = 44 * totalBooksUpdated; Console.WriteLine(totalCopiesAdded); }
static void BookCopies(BookSystemDbContext context) { var books = context.Authors .Select(a => new { a.FirstName, a.LastName, BookCopies = a.Books.Sum(b => b.Copies) }) .OrderByDescending(b => b.BookCopies) .ToList(); books.ForEach(a => Console.WriteLine($"{a.FirstName} {a.LastName} - {a.BookCopies}")); }
private static void StoredProcedure(BookSystemDbContext context) { var nameArgs = Console.ReadLine().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); var firstName = nameArgs[0]; var lastName = nameArgs[1]; SqlParameter firstNameParameter = new SqlParameter("@FirstName", firstName); SqlParameter lastNameParameter = new SqlParameter("@LastName", lastName); var totalBooks = context.Database .SqlQuery <int>("exec usp_GetToalNumberOfBooksForAuthor @FirstName, @LastName", firstNameParameter, lastNameParameter) .FirstOrDefault(); Console.WriteLine($"{firstName} {lastName} has written {totalBooks} books"); }
static void SeacrhByAuthorLastName(BookSystemDbContext context) { string input = Console.ReadLine().ToLower(); var books = context.Books .Where(b => b.Author.LastName.StartsWith(input)) .Select(b => new { b.Title, b.Author.FirstName, b.Author.LastName }) .ToList(); books.ForEach(b => Console.WriteLine($"{b.Title} ({b.FirstName} {b.LastName})")); }
static void FindProfit(BookSystemDbContext context) { var result = context.Categories.Select(c => new { Name = c.Name, TotalProfit = c.Books.Sum(b => b.Price * b.Copies) }) .OrderByDescending(b => b.TotalProfit) .ThenBy(c => c.Name) .ToList(); foreach (var item in result) { Console.WriteLine($"{item.Name} - ${item.TotalProfit:F2}"); } }
static void AuthorsSearch(BookSystemDbContext context) { string inputAuthor = Console.ReadLine(); var authors = context.Authors .Where(a => a.FirstName.EndsWith(inputAuthor)) .Select(a => new { a.FirstName, a.LastName }) .ToList(); foreach (var author in authors) { Console.WriteLine($"{author.FirstName} {author.LastName}"); } }
static void BooksReleasedBeforeDate(BookSystemDbContext context) { var inputDate = DateTime.ParseExact(Console.ReadLine(), "dd-MM-yyyy", CultureInfo.CreateSpecificCulture("en-US")); var books = context.Books .Where(b => b.ReleaseDate < inputDate) .Select(b => new { b.Title, b.Edition, b.Price }) .ToList(); foreach (var book in books) { Console.WriteLine($"{book.Title} - {book.Edition} - {book.Price}"); } }
private void SeedAuthors(BookSystemDbContext context) { string[] authors = File.ReadAllLines($@"{AppDomain.CurrentDomain.BaseDirectory}\Import\authors.csv"); // Foreach all authors and add them to database. // Skip first row because it is only describing column names. for (int i = 1; i < authors.Length; i++) { string[] data = authors[i].Split(','); string firstName = data[0].Replace("\"", string.Empty); string lastName = data[1].Replace("\"", string.Empty); Author author = new Author() { FirstName = firstName, LastName = lastName }; // When adding we may want to check if author with same first name and last name is existing. context.Authors.AddOrUpdate(a => new { a.FirstName, a.LastName }, author); } }
static void Main() { var dbContext = new BookSystemDbContext(); /*01*/ //AgeRestriction(dbContext); /*02*/ //GoldenBooks(dbContext); /*03*/ //BooksByPrice(dbContext); /*04*/ //NotReleasedBooks(dbContext); /*05*/ //BookTitlesByCategory(dbContext); /*06*/ //BooksReleasedBeforeDate(dbContext); /*07*/ //AuthorsSearch(dbContext); /*08*/ //SearchBook(dbContext); /*09*/ //SeacrhByAuthorLastName(dbContext); /*10*/ //CountBooks(dbContext); /*11*/ //BookCopies(dbContext); /*12*/ //FindProfit(dbContext); /*13*/ //MostRecentBooks(dbContext); /*14*/ //IncreaseBookCopies(dbContext); /*15*/ //RemoveBooks(dbContext); /*16*/ //StoredProcedure(dbContext); }
static void BookTitlesByCategory(BookSystemDbContext context) { Console.WriteLine("Categories to choose: RandomCategory1 to RandomCategory8"); var categoryNames = Console.ReadLine().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); var resultCategories = new List <Category>(); var resultCategoriesWithBooks = context.Categories .Where(c => categoryNames.Contains(c.Name)) .SelectMany(c => c.Books.Select(b => new { Id = b.Id, Title = b.Title })) .ToList(); if (resultCategoriesWithBooks.Count < 1) { Console.WriteLine("Try with RandomCategory1 or RandomCategory2...."); } else { foreach (var book in resultCategoriesWithBooks) { Console.WriteLine(book.Title); } } }
private void SeedBooks(BookSystemDbContext context) { int authorsCount = context.Authors.Local.Count; string[] books = File.ReadAllLines($@"{AppDomain.CurrentDomain.BaseDirectory}\Import\books.csv"); for (int i = 1; i < books.Length; i++) { string[] data = books[i] .Split(',') .Select(arg => arg.Replace("\"", string.Empty)) .ToArray(); int authorIndex = i % authorsCount; Author author = context.Authors.Local[authorIndex]; EditionType edition = (EditionType)int.Parse(data[0]); DateTime releaseDate = DateTime.ParseExact(data[1], "d/M/yyyy", CultureInfo.InvariantCulture); int copies = int.Parse(data[2]); decimal price = decimal.Parse(data[3]); Restriction ageRestriction = (Restriction)int.Parse(data[4]); string title = data[5]; Book book = new Book { Author = author, AuthorId = author.Id, Edition = edition.ToString(), ReleaseDate = releaseDate, Copies = copies, Price = price, AgeRestriction = ageRestriction.ToString(), Title = title }; context.Books.AddOrUpdate(b => new { b.Title, b.AuthorId }, book); } }