We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Calculate Average of value using two tables RSS

## 2 replies

Last post May 29, 2020 12:28 PM by PaulTheSmith

• polunyt

Member

2 Points

10 Posts

### Calculate Average of value using two tables

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();```

• yij sun

Contributor

4210 Points

1655 Posts

### Re: Calculate Average of value using two tables

May 28, 2020 07:44 AM|yij sun|LINK

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

ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
• PaulTheSmith

Participant

1680 Points

964 Posts

### Re: Calculate Average of value using two tables

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,