public IEnumerable <Review> GetAllPaginated(long bookId, Pagination paginationFilter)
{
_crudRepository.Get <Book>(bookId);
using (var connection = _databaseProvider.GetConnection())
{
var parameters = new
{
Limit = paginationFilter.Limit,
OffSet = paginationFilter.CalculateOffSet(),
BookId = bookId
};
string sql = @"SELECT *
FROM Review r
INNER JOIN User u ON r.UserId = u.Id
INNER JOIN Book b ON r.BookId = b.Id
WHERE BookId = @BookId
ORDER BY r.Id
LIMIT @Limit
OFFSET @OffSet;";
var reviews = connection.Query <Review, User, Book, Review>(
sql,
(review, user, book) =>
{
review.User = user;
review.Book = book;
return(review);
},
parameters);
return(reviews);
}
}
public IEnumerable <User> GetAllPaginated(Pagination paginationFilter)
{
using (var connection = _databaseProvider.GetConnection())
{
var parameters = new
{
Limit = paginationFilter.Limit,
OffSet = paginationFilter.CalculateOffSet()
};
string sql = @"SELECT * FROM User
LIMIT @Limit
OFFSET @OffSet;";
return(connection.Query <User>(sql, parameters));
}
}
public IEnumerable <Rental> GetAllPaginated(Pagination paginationFilter)
{
using (var connection = _databaseProvider.GetConnection())
{
var parameters = new
{
Limit = paginationFilter.Limit,
OffSet = paginationFilter.CalculateOffSet()
};
var query = @"SELECT *
FROM Rental r
INNER JOIN User u ON r.IdUser = u.Id
INNER JOIN Book b ON r.IdBook = b.Id
LIMIT @Limit
OFFSET @OffSet;";
var rentalList = connection
.Query(query, (Func <Rental, User, Book, Rental>)IncludeUserAndBook, parameters)
.ToList();
return(rentalList);
}
}