// function that returns the top rated authors from database
public List <AuthorListViewModel> GetTopRatedAuthors()
{
// Gets a list of all the user ratings the author has resieved for all his books
var topRatedBooks = (from b in _db.Books
join r in _db.Reviews on b.Id equals r.BookId
select new AuthorListViewModel
{
Id = b.AuthorId,
Rating = r.Rating
});
// Top rated authors are calculated from the table above,
// by getting the average of the authors user ratings
var topRatedAuthors = (from a in _db.Authors
join b in topRatedBooks on a.Id equals b.Id
orderby b.Rating descending
group b by new { b.Id, a.Name } into BooksRated
select new AuthorListViewModel
{
Name = BooksRated.Key.Name,
Rating = BooksRated.Average(a => a.Rating)
});
// Making sure the list is ordered correctly
var topTen = topRatedAuthors.OrderByDescending(average => average.Rating);
return(topTen.Take(10).ToList());
}
// Function that returns a BookListViewModel with all the info from the book with the chosen ID
public BookListViewModel GetBookDetails(int Id)
{
// Get the rating of the book
var getRating = (from b in _db.Books
join r in _db.Reviews on b.Id equals r.BookId
group r by new { r.BookId, b.Id } into BooksRated
select new BookListViewModel
{
BookId = BooksRated.Key.Id,
Rating = BooksRated.Average(a => a.Rating)
});
// Get the info from the database about the book and stores it in a new instance of BookListViewModel
var book = (from b in _db.Books
join au in _db.Authors on b.AuthorId equals au.Id
where b.Id == Id
join r in getRating on b.Id equals r.BookId
select new BookListViewModel
{
BookId = b.Id,
Title = b.Title,
Genre = b.Genre,
About = b.About,
Author = au.Name,
Rating = r.Rating,
Price = b.Price
}).FirstOrDefault();
return(book);
}
// Function that returns the top user rated books from database
public List <BookListViewModel> GetTopRatedBooks()
{
// Average of the user ratings from each book is calculated
var topRatedBooks = (from b in _db.Books
join r in _db.Reviews on b.Id equals r.BookId
group r by new { r.BookId, b.Id } into BooksRated
select new BookListViewModel
{
BookId = BooksRated.Key.Id,
Rating = BooksRated.Average(a => a.Rating)
});
// Top rated books are ordered in descending order
var topTen = topRatedBooks.OrderByDescending(average => average.Rating);
// The rating found and put in the new instance fo the BookListViewModel
var topReturn = (from b in _db.Books
join au in _db.Authors on b.AuthorId equals au.Id
join r in topTen on b.Id equals r.BookId
orderby r.Rating descending
select new BookListViewModel
{
BookId = b.Id,
Title = b.Title,
Genre = b.Genre,
About = b.About,
Rating = r.Rating,
Author = au.Name,
Price = b.Price
}).Take(10);
return(topReturn.ToList());
}
// function that returns a list of all the books in the database
public List <BookListViewModel> GetAllBooks()
{
var getRating = (from b in _db.Books
join r in _db.Reviews on b.Id equals r.BookId
group r by new { r.BookId, b.Id } into BooksRated
select new BookListViewModel
{
BookId = BooksRated.Key.Id,
Rating = BooksRated.Average(a => a.Rating)
});
var books = (from b in _db.Books
join au in _db.Authors on b.AuthorId equals au.Id
join r in getRating on b.Id equals r.BookId
select new BookListViewModel
{
BookId = b.Id,
Title = b.Title,
Genre = b.Genre,
About = b.About,
Rating = r.Rating,
Author = au.Name,
Price = b.Price
}).ToList();
return(books);
}
// function that uses the search-field to return a list of searched books
public List <BookListViewModel> GetSearchedBooks(int genre, int order)
{
var getRating = (from b in _db.Books
join r in _db.Reviews on b.Id equals r.BookId
group r by new { r.BookId, b.Id } into BooksRated
select new BookListViewModel
{
BookId = BooksRated.Key.Id,
Rating = BooksRated.Average(a => a.Rating)
});
var filteredBooks = (from b in _db.Books
join au in _db.Authors on b.AuthorId equals au.Id
join r in getRating on b.Id equals r.BookId
select new BookListViewModel
{
BookId = b.Id,
Title = b.Title,
Genre = b.Genre,
About = b.About,
Rating = r.Rating,
Author = au.Name,
Price = b.Price
});
// Genres
if (genre == 1)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Adventure"));
}
else if (genre == 2)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Autobiography"));
}
else if (genre == 3)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Fiction"));
}
else if (genre == 4)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Studies"));
}
else if (genre == 5)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Romance"));
}
else if (genre == 6)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Cookbooks"));
}
else if (genre == 7)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("ScienceFiction"));
}
else if (genre == 8)
{
filteredBooks = filteredBooks.Where(book => book.Genre.Equals("Novel"));
}
// Orders
if (order == 1)
{
filteredBooks = filteredBooks.OrderBy(book => book.Price);
}
else if (order == 2)
{
filteredBooks = filteredBooks.OrderByDescending(book => book.Price);
}
else if (order == 3 || order == 0)
{
filteredBooks = filteredBooks.OrderBy(book => book.Title);
}
else if (order == 4)
{
filteredBooks = filteredBooks.OrderByDescending(book => book.Title);
}
// Return value
var output = filteredBooks.ToList();
return(output);
}
