How to combine Join with an aggregate function and group by

Question

I am trying to get the average rating of all restaurants and return the names of all resteraunts associated with that id, I was able to write a sql statement to get the average of restaurants along with the names of the restaurants however I want to only return the name of the restaurant once.

Select  t.Average,  Name from [dbo].[Reviews] as rev
join [dbo].[Resteraunts] as rest
on rest.ResterauntId = rev.ResterauntId
inner join 

(
 SELECT [ResterauntId],

     Avg(Rating)     AS Average
   FROM [dbo].[Reviews]
   GROUP BY [ResterauntId]


)
as t on t.ResterauntId = rest.ResterauntId

resteraunt class

public int ResterauntId { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }


public virtual ICollection<Review> Reviews { get; set; }

public virtual Review  reviews{ get; set; }

Review class

public int ReviewId { get; set; }

        public double   Rating { get; set; }

        [ForeignKey("ResterauntId")]
        Resteraunt  Resteraunt { get; set; }
        public int ResterauntId { get; set; }
        public DateTime DateOfReview { get; set; }

If possible I would like to have the answer converted to linq.

Answer 1

Resteurants.Select(r => new {
    Average = r.Reviews.Average(rev => rev.Rating),
    r.Name
})

This should give you a set of objects that have Average (the average of all reviews for that restaurant) and the Name of the restaurant.

This assumes that you have correctly setup the relationships so that Restaurant.Reviews only refers to the ones that match by ID.

If you don't have that relationship setup and you need to filter it yourself:

Resteurants.Select(r => new {
    Average = Reviews.Where(rev => rev.ResteurantId == r.Id).Average(rev => rev.Rating),
    r.Name
})

Answer 2

Firstly your models seems to have more aggregation than required, I have taken the liberty to trim it and remove extra fields, ideally all that you need a Relation ship between two models RestaurantId (Primary Key for Restaurant and Foreign Key (1:N) for Review)

public class Restaurant
{
    public int RestaurantId { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public virtual ICollection<Review> Reviews { get; set; }
}

public class Review
{
    public int ReviewId { get; set; }
    public double Rating { get; set; }  
    public int RestaurantId { get; set; }
    public DateTime DateOfReview { get; set; }
}

If these are the models, then you just need List<Restaurant> restaurantList, since that internally contains the review collection, then all that you need is:

var result = 
    restaurantList.Select(x => new {
                                    Name = x.Name,
                                    Average = x.Reviews.Average(y => y.Rating)
                                   }
                         );

In case collection aggregation is not there and you have separate ReviewList as follows: List<Review> reviewList, then do the following:

var result = 
    reviewList.GroupBy(x => x.RestaurantId, x => new {x.RestaurantId,x.Rating})
              .Join(restaurantList, x => x.Key,y => y.RestaurantId,(x,y) => new {
                Name = y.Name,
                AvgRating = x.Average(s => s.Rating)                
              });

Also please note this will only List the Restaurants, which have atleast one review, since we are using InnerJoin, otherwise you need LeftOuterJoin using GroupJoin, for Restaurants with 0 Rating

Answer 3

I see your Restaurant class already has an ICollection<Review> that represents the reviews of the restaurant. This is probably made possible because you use Entity Framework or something similar.

Having such a collection makes the use of a join unnecessary:

var averageRestaurantReview = Restaurants
    .Select(restaurant => new
    .Where(restaurant => ....) // only if you don't want all restaurants
    {
        Name = restaurant.Name,
        AverageReview = restaurants.Reviews
            .Select(review => review.Rating)
            .Average(),
    });

Entity Framework will do the proper joins for you.

If you really want to use something join-like you'd need Enumerable.GroupJoin

var averageRestaurantReview = Restaurants
    .GroupJoin(Reviews,                   // GroupJoin Restaurants and Reviews
        restaurant => restaurant.Id,      // From every restaurant take the Id
        review => review.RestaurantId,    // From every Review take the RestaurantId
    .Select( (restaurant, reviews) => new // take the restaurant with all matching reviews
    .Where(restaurant => ....)            // only if you don't want all restaurants
    {                                     // the rest is as before
        Name = restaurant.Name,
        AverageReview = reviews
            .Select(review => review.Rating)
            .Average(),
    });