Calculation of the average in linq

user_16047143 0
Viewed 81

I want to get the average rating of each book. The problem is, I don't know how to do that in linq.

Code:

 public class Review
{
    public int Id { get; set; }
    public string Content { get; set; }
    public int Rating { get; set; }
    public virtual Book Book { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public virtual ICollection<Review> Reviews { get; set; }
}

In sql it would look like this:

select Books.Title, Avg(Reviews.Rating)
from Books
  inner join Reviews on Books.Id = Reviews.BookId
group by Books.Title

Output exemple:

Title | Rating
Book1 | 5
Book2 | 2
Book3 | 3

How can I do this query in linq c #?

linq c#
1 Answers

If the Reviews property contains all Reviews for a particular Book this is simple:

var result = books.Select(book => new
{
    book.Title,
    Rating = book.Reviews.Average(review => review.Rating)
});

But if you have separate collections you will need to perform a join:

var result = books.GroupJoin(reviews, // One-to-many        
    book => book.Id,                  // Primary key
    review => review.Book.Id,         // Foreign key
    (book, reviews) => new            // Selection
    {
        book.Title,
        Rating = reviews.Average(review => review.Rating)
    });
user_81263620
Related