LINQ Query for Average of column with a join, grouping by the key of the joined table

Question

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.

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.

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

The query in LINQ without LAMBDA syntax which works:

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

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);
            });

How do your entity classes look like? Do you have navigation properties between the two entities? — Yacoub Massad, May 23 '16 at 20:05
I've edited the post with the navigation properties you requested. — Sebastiaan Steenbrink, May 23 '16 at 20:20
Why join the two tables if you don't need the name of the movie from first table? Movie Id and avg review can be calculated alone from the 2nd table. — vabii, May 23 '16 at 20:29
I think the error starts with a wrong mapping between `Movie` and `CustomerFeedback`. It should be 1:n. — Gert Arnold, May 23 '16 at 20:34

vabii · Answer 1 · 2016-05-23T21:48:15.063

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;
}

edited May 23 '16 at 21:48

answered May 23 '16 at 20:43

vabii

521
6
16

The foreach loop does not accept anonymous types so I can't print the results to check if it works.. – Sebastiaan Steenbrink May 23 '16 at 21:17
What is the error (compile or runtime) you are getting? The above code works and prints on console perfectly on my machine. – vabii May 23 '16 at 21:22
An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context. Runtime I believe, I'm not sure how I can check – Sebastiaan Steenbrink May 23 '16 at 21:31
How about if you do `finalReviews.AsEnumerable()`, I edited the answer. I looked into - http://stackoverflow.com/questions/18929483/unable-to-create-a-constant-value-of-type-only-primitive-types-or-enumeration-ty – vabii May 23 '16 at 21:41
I added `ToList()` to the query. Hopefully, it will run the query against EF. Sorry, I am not that good with EF details. Could you try now? – vabii May 23 '16 at 21:49

LINQ Query for Average of column with a join, grouping by the key of the joined table

1 Answers1