private static void IncreaseBookCopies(BookShopLabContext context)
{
Console.WriteLine("Please enter a date:");
DateTime date = DateTime.Parse(Console.ReadLine());
Console.WriteLine("Please enter book copies (int):");
int bookCopies = int.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.ReleaseDate > date);
Console.WriteLine("Result: ");
Console.WriteLine(books.Count() * bookCopies);
var updatedBooks = context.Books
.Where(b => b.ReleaseDate > date)
.Update(b => new Book()
{
Copies = b.Copies + bookCopies
});
Console.WriteLine("Updated Books: {0}", updatedBooks);
context.SaveChanges();
}
private static void Main(string[] args)
{
var context = new BookShopLabContext();
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-EN");
//var count = context.Books.Count();
// 1. Get all books after the year 2000. Print only their titles.
// GetAllBooksAfter2000(context);
// 2. Get all authors with at least one book with release date before 1990.Print their first name and last name.
//GetAllAuthorsBooksBefore1990(context);
// 3. Get all authors, ordered by the number of their books (descending). Print their first name, last name and book count.
// AuthorsOrderedByBooksQty(context);
// 4. Get all books from author George Powell, ordered by their release date (descending),
// then by book title (ascending). Print the book's title, release date and copies.
//AllGeorgePowellBooks(context);
//5. **Get the most recent books by categories.
// The categories should be ordered by total book count. Only take the top 3 most recent books from each category
// - ordered by date (descending), then by title (ascending). Select the category name, total book count and for each book
// - its title and release date.
//MostRecentBooksByCategory(context);
//7. - Related Books
GetRelatedBooks(context);
}
private static void MostRecentBooksByCategory(BookShopLabContext context)
{
var categories = context.Categories
.Select(c => new
{
c.Name,
c.Books,
BooksCount = c.Books.Count
})
.OrderByDescending(c => c.BooksCount);
foreach (var category in categories)
{
Console.WriteLine($"---{category.Name}: {category.BooksCount}");
var books = category.Books
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Take(3)
.Select(b => new
{
b.Title,
b.ReleaseDate
});
foreach (var book in books)
{
Console.WriteLine($"{book.Title} ({book.ReleaseDate.Value.Year})");
}
}
}
private static void GetRelatedBooks(BookShopLabContext context)
{
var books = context.Books
.Take(3)
.ToList();
books[0].RelatedBooks.Add(books[1]);
books[1].RelatedBooks.Add(books[0]);
books[0].RelatedBooks.Add(books[2]);
books[2].RelatedBooks.Add(books[0]);
context.SaveChanges();
var booksFromQuery = context.Books
.Select(b => new
{
b.Title,
relatedBooks = b.RelatedBooks
})
.Take(3);
foreach (var book in booksFromQuery)
{
Console.WriteLine("--{0}", book.Title);
foreach (var b in book.relatedBooks)
{
Console.WriteLine(b.Title);
}
}
}
private static void MosteRecentBooks(BookShopLabContext context)
{
var categories = context.Categories
.Where(c => c.Books.Count > 35)
.Select(c => new
{
c.Name,
c.Books.Count,
RecentBooks = c.Books
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Take(3)
.Select(b => new
{
b.Title,
b.ReleaseDate
})
});
foreach (var category in categories)
{
Console.WriteLine($"{category.Name} - {category.Count} books");
foreach (var book in category.RecentBooks)
{
Console.WriteLine($"{book.Title} - {book.ReleaseDate}");
}
Console.WriteLine();
}
}
private static void GetGoldenBooks(BookShopLabContext context)
{
var books = context.Books
.Where(b => b.EditionType == EditionType.Gold && b.Copies < 5000)
.Select(b => b.Title);
PrintBooks(books);
}
private static void GetNotReleasedBook(BookShopLabContext context)
{
Console.WriteLine("Please enter an year");
int year = int.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.ReleaseDate.Value.Year != year)
.Select(b => b.Title);
PrintBooks(books);
}
private static void BookTitleByCategory(BookShopLabContext context)
{
Console.WriteLine("Enter categories separated by a space");
var categories = Console.ReadLine().Split(' ').ToList();
var books = context.Books
.Where(b => b.Categories.Count(c => categories.Contains(c.Name)) != 0)
.Select(b => b.Title);
PrintBooks(books);
}
private static void CountBooks(BookShopLabContext context)
{
Console.WriteLine("Please enter a number");
int lenght = int.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.Title.Length > lenght);
Console.WriteLine(books.Count());
}
private static void RemoveBooks(BookShopLabContext context)
{
Console.WriteLine("Please enter number (int):");
int number = int.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.Copies < number)
.Delete();
Console.WriteLine($"Deleted books: {books}");
context.SaveChanges();
}
private static void GetBookTitleByAgeRestriction(BookShopLabContext context)
{
Console.WriteLine("Please enter a command:");
string ageRestriction = Console.ReadLine().ToLower();
var books = context.Books
.Where(b => b.AgeRestriction.ToString().ToLower() == ageRestriction)
.Select(b => b.Title);
PrintBooks(books);
}
private static void GetAllBooksAfter2000(BookShopLabContext context)
{
var allBooksAfter2000 = context.Books
.Where(b => b.ReleaseDate.Value.Year > 2000)
.Select(b => b.Title);
foreach (var title in allBooksAfter2000)
{
Console.WriteLine(title);
}
}
private static void BooksSearch(BookShopLabContext context)
{
Console.WriteLine("Please enter string");
string containingString = Console.ReadLine();
var books = context.Books
.Where(b => b.Title.ToLower().Contains(containingString.ToLower()))
.Select(b => b.Title);
PrintBooks(books);
}
private static void BookTitlesSearch(BookShopLabContext context)
{
Console.WriteLine("Please enter string");
string startString = Console.ReadLine();
var books = context.Books
.Where(b => b.Author.LastName.StartsWith(startString))
.Select(b => b.Title);
PrintBooks(books);
}
private static void Main(string[] args)
{
var context = new BookShopLabContext();
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-EN");
//var count = context.Books.Count();
// 2. Books Titles by Age Restriction
// GetBookTitleByAgeRestriction(context);
// 3. Golden Books
// GetGoldenBooks(context);
// 4. Books By Price
// GetBooksByPrice(context);
// 5. Not Released Books
// GetNotReleasedBook(context);
// 6. BookTitleByCategory
// BookTitleByCategory(context);
// 7.Books Released Before Date
// BooksReleasedBeforeDate(context);
// 8. Authors Searched
// Write a program that selects and prints names of those authors whose first name end with given string.
// AuthorsSearch(context);
// 9. Books Search
// Write a program that selects and prints titles of books which contains given string (regardless of the casing).
// BooksSearch(context);
// 10. Book Titles Search
// BookTitlesSearch(context);
// 11. Count Books
// CountBooks(context);
// 12. Total Book Copies
// TotalBookCopies(context);
// 13. Find Profit
// FindProfit(context);
// 14. MostRecentBooks
// MosteRecentBooks(context);
// 15. Increase book copies
//IncreaseBookCopies(context);
// 16. Remove Books
// RemoveBooks(context);
}
private static void TotalBookCopies(BookShopLabContext context)
{
var authors = context.Authors
.GroupBy(a => new
{
Author = a.FirstName + " " + a.LastName,
Copies = a.Books.Sum(b => b.Copies)
})
.OrderByDescending(a => a.Key.Copies);
foreach (var author in authors)
{
Console.WriteLine($"{author.Key.Author} - {author.Key.Copies}");
}
}
private static void GetBooksByPrice(BookShopLabContext context)
{
var books = context.Books
.Where(b => b.Price > 5 && b.Price < 40)
.Select(b => new
{
title = b.Title,
price = b.Price
});
foreach (var book in books)
{
Console.WriteLine($"{book.title} - ${book.price}");
}
}
private static void GetAllAuthorsBooksBefore1990(BookShopLabContext context)
{
var authors = context.Books
.Where(b => b.ReleaseDate.Value.Year < 1990).Select(b => new
{
b.Author.FirstName,
b.Author.LastName
})
.Distinct();
foreach (var author in authors)
{
Console.WriteLine($"{author.FirstName} {author.LastName}");
}
}
private static void AuthorsOrderedByBooksQty(BookShopLabContext context)
{
var authors = context.Authors
.OrderByDescending(a => a.Books.Count)
.Select(a => new
{
a.FirstName,
a.LastName,
booksCount = a.Books.Count
});
foreach (var author in authors)
{
Console.WriteLine($"{author.FirstName} {author.LastName}, books: {author.booksCount}");
}
}
private static void FindProfit(BookShopLabContext context)
{
var categories = context.Categories
.GroupBy(c => new
{
CategoryName = c.Name,
Profit = c.Books.Sum(b => b.Price * b.Copies)
})
.OrderByDescending(c => c.Key.Profit)
.ThenBy(c => c.Key.CategoryName);
foreach (var category in categories)
{
Console.WriteLine($"{category.Key.CategoryName} - ${category.Key.Profit}");
}
}
private static void AllGeorgePowellBooks(BookShopLabContext context)
{
var books = context.Books
.Where(b => b.Author.FirstName == "George" && b.Author.LastName == "Powell")
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Select(b => new
{
b.Title,
b.ReleaseDate,
b.Copies
});
foreach (var book in books)
{
Console.WriteLine($"{book.Title} {book.ReleaseDate} {book.Copies}");
}
}
private static void AuthorsSearch(BookShopLabContext context)
{
Console.WriteLine("Please enter string");
string endString = Console.ReadLine();
var authors = context.Authors
.Where(a => a.FirstName.EndsWith(endString))
.Select(a => new
{
a.FirstName,
a.LastName
});
foreach (var author in authors)
{
Console.WriteLine($"{author.FirstName} {author.LastName}");
}
}
private static void BooksReleasedBeforeDate(BookShopLabContext context)
{
Console.WriteLine("Please enter date");
DateTime date = DateTime.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.ReleaseDate < date)
.Select(b => new
{
b.Title,
b.EditionType,
b.Price
});
foreach (var book in books)
{
Console.WriteLine($"{book.Title} - {book.EditionType} - {book.Price}");
}
}
