CODEWITHSUNDEEP
c#linqasp.net-mvc-4asp.net-web-api
Ayman H
Ayman H

Reputation: 185

Finding Average of value based on count of rows in LINQ

I have a Restaurant review application that has a separate Restaurant and Reviews table. I have built a MVC WebAPI that has a method to passes a Restaurant object based on the ID of the restaurant.

As part of the object returned I would like to add an average rating value, which is based on the sum of the rating values entered on the Reviews table divided for the restaurant Id by the total count of reviews entered for the restaurant Id.

It would be great if the value is in a one decimal place format.

I am able to get the average value(no decimal places) from the Reviews table using the following code

var sum = (from a in db.Reviews
           where a.ReviewsRestaurantID.Equals(id)
           select a.ReviewsRating).Sum();

var count = (from a in db.Reviews
             where a.ReviewsRestaurantID.Equals(id)
             select a.ReviewsRating).Count();

decimal average = sum / count;

I am not sure how I can add the value to my query used to return the Restaurant by ID method (pls see below). Can someone please help in adding the custom average rating value to the method below or is there a way to do a join on the reviews table and calculate the value more efficiently? 

public IQueryable<Restaurant> GetRestaurantByID(int id)
{ 
  var query = from a in db.Restaurants
              where a.RestaurantID.Equals(id)
              select a;

  return query; 
}

Upvotes: 0

Views: 4001

Answers (3)

A.R.
A.R.

Reputation: 15685

For starters, you can really pare down your code + traffic to the server. Since you mentioned tables, rows, and db, I would guess that you are going to some SQL server.

Start by getting all of the stuff you care about from the server, i.e.

var reviews = (from x in db.Reviews where a.ReviewsRestaurantID.Equals(id) select x).ToList();

Now you don't have to make two seperate trips to the server to collect the data you care about. You can just run and 'average' query on the data you now have in memory...

// Assuming, from your sample that the ratings are stored as decimals....
// This will do the counting, summing, dividing, etc.
decimal avg = (from x in reviews select x.Rating).Average();

Upvotes: 3

Hogan
Hogan

Reputation: 70523

You could just use average:

var average = (from a in db.Reviews
               where a.ReviewsRestaurantID.Equals(id)
               select a.ReviewsRating).Average();

You could also do it with aggregate like this (if what you really want to do is more complicated, here is an example implementation of average):

var result = db.Reviews.Where(r => r.ReviewsRestaurantID = id)
     .Aggregate(new () { total = 0, count = 0, avg = 0.0 },
          (o, n) => {
             var result = new () { total = o.total+n.ReviewsRating, count = o.count+1, avg = 0.0};
             result.avg = result.total / result.count;
             return result;
       }, (r) => r.avg);

Upvotes: 1

King King
King King

Reputation: 63317

var resReviews = from x in db.Restaurants 
                 join y in db.Reviews on x.RestaurantID equals y.ReviewsRestaurantID into z 
                 from a in z
                 group a by a.RestaurantID into g 
                 select new {
                    RestaurantInfo = g.FirstOrDefault(),
                    RatingAverage = g.Average(x=>x.ReviewsRating)
                 };

Upvotes: 2

Related Questions