public IEnumerable <ImageWithLikes> GetUserLikedImages(int userId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = connection.CreateCommand();
command.CommandText = @"SELECT i.*, COUNT(il.ImageId) as 'LikesCount'
FROM Images i
JOIN ImageLikes il
ON i.Id = il.ImageId
WHERE il.UserId = @userId
GROUP BY i.Id, i.FirstName, i.LastName, i.ImageFile, i.DateUploaded, i.ViewCount";
command.Parameters.AddWithValue("@userId", userId);
connection.Open();
var reader = command.ExecuteReader();
List <ImageWithLikes> images = new List <ImageWithLikes>();
while (reader.Read())
{
Image image = ToImage(reader);
ImageWithLikes imageWithLikes = new ImageWithLikes
{
Image = image,
LikesCount = (int)reader["LikesCount"]
};
images.Add(imageWithLikes);
}
return(images);
}
}
public IEnumerable <ImageWithLikes> GetFiveMostLikedImages()
{
using (var connection = new SqlConnection(_connectionString))
{
var command = connection.CreateCommand();
command.CommandText = @"SELECT TOP 5 i.*, COUNT(il.ImageId) as 'LikesCount'
FROM Images i
LEFT JOIN ImageLikes il
ON i.Id = il.ImageId
GROUP BY i.Id, i.FirstName, i.LastName, i.ImageFile, i.DateUploaded, i.ViewCount
ORDER BY Count(il.ImageId) DESC";
connection.Open();
var reader = command.ExecuteReader();
List <ImageWithLikes> images = new List <ImageWithLikes>();
while (reader.Read())
{
Image image = ToImage(reader);
ImageWithLikes imageWithLikes = new ImageWithLikes
{
Image = image,
LikesCount = (int)reader["LikesCount"]
};
images.Add(imageWithLikes);
}
return(images);
}
}