locked
Calculate Average of value using two tables RRS feed

  • Question

  • User-865174439 posted

    In project i have two tables

    • Restaurant (RestaurantId, Name, Adress)
    • 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>
                {
                    new Restaurant {RestaurantId=1,Name="A",Adress="A@dfsfw"},
                    new Restaurant {RestaurantId=2,Name="B",Adress="B@dfwfgrw"},
                    new Restaurant {RestaurantId=3,Name="C",Adress="C@trhh"},
               };
                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.Address, 
                                      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, 
                             r.Address, 
                             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