LINQ查询具有连接的列的平均值，按连接表的键进行分组

I have a question regarding LINQ queries using lambda expressions. I have a fairly simple SQL query which takes the average of the rating in table CustomerFeedback, and joins the Movie table on the key movie_id.

我有一个关于使用lambda表达式的LINQ查询的问题。我有一个相当简单的SQL查询,它取表CustomerFeedback中的评级的平均值,并加入关键movie_id上​​的Movie表。

I have written the query in SQL and LINQ sql syntax, but writing it in lambda syntax (method syntax) has been a struggle for me.

我已经用SQL和LINQ sql语法编写了查询,但是用lambda语法(方法语法)编写它对我来说很困难。

This is a primitive version of the database scheme:

这是数据库方案的原始版本:

TABLE Movie (
    movie_id INT NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL 
    )

    TABLE CustomerFeedback (
    movie_id int, 
    rating int
    CONSTRAINT fk_CustomerFeedbackMovie FOREIGN KEY (movie_id) REFERENCES Movie (movie_id) ON UPDATE CASCADE
    )

The query in SQL: SELECT avg(rating) as AvgRating , m.movie_id as MovieID FROM CustomerFeedback cf INNER JOIN Movie m ON m.movie_id = cf.movie_id GROUP BY m.movie_id

SQL中的查询:SELECT avg(rating)as AvgRating,m.movi​​e_id as MovieID FROM CustomerFeedback cf INNER JOIN Movie m ON m.movi​​e_id = cf.movi​​e_id GROUP BY m.movi​​e_id

The query in LINQ without LAMBDA syntax which works:

LINQ中没有LAMBDA语法的查询有效:

var averages =(from movie in _context.Movie
                            join feedback in _context.CustomerFeedback on movie.movie_id equals feedback.movie_id
                            group new {movie, feedback } by new
                            {
                                movie.movie_id
                            }
                            into grouping
                            orderby
                                (double?)grouping.Average(p => p.feedback.rating) ascending 
                            select new
                            {
                                grouping.Key.movie_id,
                                rating_average = (double?)grouping.Average(p => p.feedback.rating)
                            }).Take(10);

If anyone could give me a few pointers or a solution it would be greatly appreciated!

如果有人能给我一些指示或解决方案,将不胜感激!

EDIT: The navigation properties as requested by user YacoubMassad

编辑:用户YacoubMassad请求的导航属性

modelBuilder.Entity<CustomerFeedback>(entity =>
            {
                entity.HasKey(e => e.movie_id);

                entity.Property(e => e.movie_id).ValueGeneratedNever();

                entity.Property(e => e.comments)
                    .IsRequired()
                    .HasMaxLength(255)
                    .HasColumnType("varchar");

                entity.Property(e => e.customer_mail_address)
                    .IsRequired()
                    .HasMaxLength(255)
                    .HasColumnType("varchar");

                entity.Property(e => e.feedback_date).HasColumnType("date");

                entity.HasOne(d => d.customer_mail_addressNavigation).WithMany(p => p.CustomerFeedback).HasForeignKey(d => d.customer_mail_address).OnDelete(DeleteBehavior.Restrict);

                entity.HasOne(d => d.movie).WithOne(p => p.CustomerFeedback).HasForeignKey<CustomerFeedback>(d => d.movie_id).OnDelete(DeleteBehavior.Restrict);
            });

1 个解决方案

#1

List<Movie> movies = new List<Movie>() { 
            new Movie(){Id = 1, Name = "The Matrix"},
            new Movie(){Id = 2, Name = "Captain America"}
        };

        List<MovieReview> reviews = new List<MovieReview>() 
        { 
            new MovieReview(){MovieId = 1, Review = 8},
            new MovieReview(){MovieId = 1, Review = 7},
            new MovieReview(){MovieId = 2, Review = 5}
        };

        //var movieReviews = reviews.GroupBy(r => r.MovieId).Select(g => new { MovieId = g.Key, AvgReview = g.Average( r => r.Review) });


        var finalReviews = movies.Join(
            reviews.GroupBy(r => r.MovieId).Select(g => new { MovieId = g.Key, AvgReview = g.Average(r => r.Review) }),
            m => m.Id,
            r => r.MovieId,
            (m, r) => new { Name = m.Name, AvgReview = r.AvgReview }).ToList();

        foreach (var f in finalReviews)
        { 
            Console.WriteLine(f.Name + " " + f.AvgReview);
        }

        Console.ReadKey();

The entity classes

实体类

class Movie
{
    public int Id = 0;
    public string Name = "";
}

class MovieReview
{
    public int MovieId = 0;
    public int Review = 0;
}