Преобразование SQL в Linq с помощью EF Core
Я использую .NET Core 2.2, EF Core, C# и SQL Server 2017. Я не могу перевести нужный мне запрос в Linq.
Это запрос, который мне нужно преобразовать:
SELECT TOP 5
p.Id,
p.Title,
AVG(q.RatingValue) AvgRating
FROM Movies AS p
INNER JOIN Ratings AS q ON p.Id = q.MovieId
GROUP BY p.Id, p.Title
ORDER BY AvgRating DESC, p.Title ASC
Идея предыдущего запроса состоит в том, чтобы получить 5 лучших фильмов в соответствии со средним рейтингом, упорядочив их сначала по самому высокому среднему, а в случае того же среднего по алфавиту.
Пока это мой запрос, который выполняет соединение, но затем все еще отсутствует: группировка, среднее значение и порядок:
public class MovieRepository : IMovieRepository
{
private readonly MovieDbContext _moviesDbContext;
public MovieRepository(MovieDbContext moviesDbContext)
{
_moviesDbContext = moviesDbContext;
}
public IEnumerable<Movie> GetTopFive()
{
var result = _moviesDbContext.Movies.OrderByDescending(x => x.Id).Take(5).
Include(x => x.Ratings);
return result;
}
}
А это сущности:
public class Movie
{
public int Id { get; set; }
public string Title { get; set; }
public int YearOfRelease { get; set; }
public string Genre { get; set; }
public int RunningTime { get; set; }
public IList<Rating> Ratings { get; set; }
}
public class Rating
{
public int Id { get; set; }
public int MovieId { get; set; }
public int UserId { get; set; }
public decimal RatingValue { get; set; }
}
Я также пытался использовать инструмент Linqer для преобразования моего запроса в Linq, но он не работал.
Буду признателен за любую помощь в преобразовании этого запроса в LINQ для метода GetTopFive.
Спасибо
Самый высокий рейтинг 5. Таким образом, 1 фильм может иметь одну или несколько строк в рейтинговой таблице. Например, 10 пользователей могут оценить Терминатора 1, если для этого фильма среднее значение этих оценок является максимальным по сравнению с другими фильмами, которые должны быть показаны первыми.
Ждать! Я обновляю ответ
Рассмотрите также возможность группировки строк рейтинга и получения среднего значения.
Да! но я пробую другое решение.
Проверьте мой ответ! Я обновил ответ. Я проверил, работает отлично.
Ответы 3
Попробуйте следующее:
public IEnumerable<Movie> GetTopFive()
{
var result = _moviesDbContext.Ratings.GroupBy(r => r.MovieId).Select(group => new
{
MovieId = group.Key,
MovieTitle = group.Select(g => g.Movie.Title).FirstOrDefault(),
AvgRating = group.Average(g => g.RatingValue)
}).OrderByDescending(s => s.AvgRating).Take(5).ToList();
return result;
}
Это исключит фильмы, не имеющие рейтинга.
Но если вы сделаете следующее (как ответ artista_14):
public IEnumerable<Movie> GetTopFive()
{
var result = _moviesDbContext.Movies.GroupBy(x => new { x.Id, x.Title })
.Select(x => new {
Id = x.Key.Id,
Title = x.Key.Title,
Average = x.Average(y => y.Ratings.Sum(z => z.RatingValue))
}).OrderByDescending(x => x.Average).ThenBy(x => x.Title).Take(5).ToList();
return result;
}
это также будет включать фильмы, не имеющие рейтинга.
Примечание. Я вижу, что ваш класс модели Rating не содержит свойства навигации Movie. Пожалуйста, добавьте это следующим образом:
public class Rating
{
public int Id { get; set; }
public int MovieId { get; set; }
public int UserId { get; set; }
public decimal RatingValue { get; set; }
public Movie Movie { get; set; }
}
Пожалуйста, дайте мне знать, если это работает или нет! Если нет, то, пожалуйста, дайте мне знать сообщение об ошибке.
Я получил сообщение об ошибке: System.ArgumentException: «По крайней мере один объект должен реализовывать IComparable».
Ой! Я подозреваю, что! Подождите, пожалуйста!
Если ваш контроллер является контроллером API, убедитесь, что поверх вашего контроллера присутствует атрибут [Produces("application/json")].
еще не работает, получаю этот sql: SELECT [g.Movie0].[Id], [g.Movie0].[Title] FROM [Movies] AS [g.Movie0]
Мне пришлось сделать преобразование для IEnumerable
Давайте продолжить обсуждение в чате.
@MarcosF8 Проверьте чат, пожалуйста
Помогите завтра, пожалуйста!
Большое спасибо, TanvirArjel, большие усилия по решению проблемы с вашей стороны, я очень ценю вашу помощь!
Используемый вами запрос не будет работать при определенных обстоятельствах, как я описал в комментарии к этому ответу.
Попробуй это -
var data = _moviesDbContext.Movies.Include(x => x.Ratings)
.Select(x => new {
Id = x.Id,
Title = x.Title,
Average = (int?)x.Ratings.Average(y => y.RatingValue)
}).OrderByDescending(x => x.Average).ThenBy(x => x.Title).Take(5).ToList();
Ваш предыдущий запрос был правильным. Это вызовет Sequence contains no elements., если у фильма еще нет рейтинга! Помимо этого, в этом запросе есть еще одна проблема. Вы не использовали .Include(m => m.Ratings).
@TanvirArjel Спасибо за комментарий. Я также новичок в linq и lambda. Я запрашиваю таблицу фильмов напрямую, так что не думайте, что в таблице Movie использование .GroupBy(x => new {x.Id, x.Title}) не имеет значения, поэтому я удалил его. Также включение рейтинговых данных здесь не требуется, так как ему нужен только средний рейтинг, который будет рассчитываться только в БД..??
Без включения рейтинги будут нулевыми! Следовательно, будет выброшено исключение.
Include не нужен, поскольку x.Ratings уже является частью запроса. Единственное, это должен быть (int?)x.Ratings.Average(y => y.RatingValue), чтобы фильмы без рейтинга могли иметь значение null. Тогда я думаю, что это должно быть правильно, демонстрируя, что LINQ groupby не нужен. @ MarcosF8 Было бы неплохо получить немного больше отзывов, чем «не работает» :).
@GertArnold Я согласен
и, наконец, это хорошо работающий код:
var data = _moviesDbContext.Movies.Include(x => x.Ratings)
.Select(x => new MovieRating
{
Id = x.Id,
Title = x.Title,
Average = x.Ratings.Average(y => y.RatingValue)
}).OrderByDescending(x => x.Average).ThenBy(x => x.Title).Take(5).ToList();
return data;
Проблема заключалась в создании анонимного типа в выборе, поэтому эта строка решает проблему: .Select(x => new MovieRating
И это полный код метода и нового класса, который я создал для сопоставления полей выбора с конкретным типом:
public class MovieRepository : IMovieRepository
{
private readonly MovieDbContext _moviesDbContext;
public MovieRepository(MovieDbContext moviesDbContext)
{
_moviesDbContext = moviesDbContext;
}
public IEnumerable<Movie> GetAll()
{
return _moviesDbContext.Movies;
}
public IEnumerable<MovieRating> GetTopFive()
{
var result = _moviesDbContext.Movies.Include(x => x.Ratings)
.Select(x => new MovieRating
{
Id = x.Id,
Title = x.Title,
Average = x.Ratings.Average(y => y.RatingValue)
}).OrderByDescending(x => x.Average).ThenBy(x => x.Title).Take(5).ToList();
return result;
}
}
public class MovieRating
{
public int Id { get; set; }
public string Title { get; set; }
public decimal Average { get; set; }
}
Вы ожидаете топ-5 или только топ-5?