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