# Calculate Average of value using two tables

• ### Question

• User-865174439 posted

In project i have two tables

• RestaurantReview (ReviewId, RestaurantId, Mark)

One restaurant can have several opinions. I would like my application to display restaurant and the average of its ratings. I create code:

var resReviews = (
from x in _context.Restaurant
join y in _context.Reviews on x.IdRestauracji equals y.RestaurantId into z
from a in z
group a by a.RestaurantId into g
select new
{

RatingAverage = g.Average(x => Convert.ToInt32(x.Mark))
}
).ToList();

In list are saved calculated average mark. I would also like to have restaurant information saved on the list. I try create something like this, but it doesn't work:

var resReviews = (
from x in _context.Restaurant
join y in _context.Reviews on x.IdRestauracji equals y.RestaurantId into z
from a in z
group a by a.RestaurantId into g
select new
{
Restaurant = g.FirstOrDefault();
RatingAverage = g.Average(x => Convert.ToInt32(x.Mark))
}
).ToList();

Wednesday, May 27, 2020 3:55 PM

### All replies

• User1535942433 posted

Hi polunyt,

Accroding to your description and codes,I suggest you could push two tables into z and group z.

More details,you could refer to below codes:

public class Restaurant
{
public int RestaurantId { get; set; }
public string Name { get; set; }
public string Adress { get; set; }
}
public class RestaurantReview
{
public int ReviewId { get; set; }
public int RestaurantId { get; set; }
public int Mark { get; set; }
}

static void Main(string[] args)
{
List<Restaurant> Restaurants = new List<Restaurant>
{
};
List<RestaurantReview> RestaurantReviews = new List<RestaurantReview>
{
new RestaurantReview {ReviewId=1,RestaurantId=1,Mark=4},
new RestaurantReview {ReviewId=2,RestaurantId=1,Mark=5},
new RestaurantReview {ReviewId=3,RestaurantId=1,Mark=6},
new RestaurantReview {ReviewId=4,RestaurantId=2,Mark=1},
new RestaurantReview {ReviewId=5,RestaurantId=2,Mark=2},
new RestaurantReview {ReviewId=6,RestaurantId=2,Mark=3},
new RestaurantReview {ReviewId=7,RestaurantId=3,Mark=4},
new RestaurantReview {ReviewId=8,RestaurantId=3,Mark=6},
new RestaurantReview {ReviewId=9,RestaurantId=3,Mark=10},

};
var resReviews = (
from x in Restaurants
join y in RestaurantReviews on x.RestaurantId equals y.RestaurantId
select new {
x,
y
} into z
group z by z.x.RestaurantId into g
select new
{
Restaurant=g.FirstOrDefault().x.Name,
RatingAverage = g.Average(x => Convert.ToInt32(x.y.Mark))
}
).ToList();

}

Result:

Best regards,

Yijing Sun

Thursday, May 28, 2020 7:44 AM
• User303363814 posted

Show your class definitions.  If you don't have it then create a navigation property in Restaurant called Reviews?  Then

var result = _context
.Restaurant  // Table should have a plural name
.Select(r => new {
r,RestaurantId,
r.Name,
r.Reviews.Select(rev => rev.Mark).Average()});

If you have an aversion to making it easy then the, slightly, more complex version is

var result = _context
.Restaurant
.Select(r => new {
r.RestaurantId,
r.Name,
Rating = _context
.Reviews
.Where(rev=> rev.RestaurantId == r.RestaurantId)
.Select(rev=>rev.Mark)
.Average()
}
);

Really, add navigation properties and toss all that complex joining and grouping out the window.

Friday, May 29, 2020 12:28 PM