CODEWITHSUNDEEP
c#asp.netsqlasp.net-mvclinq
Dolaps
Dolaps

Reputation: 279

Return a list of averages LINQ

I am trying to make a suggestion system on my restaurant review site. It gets the average score of all reviews of a certain restaurant and then finds the average review score from all of the other restaurants that have the same cuisine as the original restaurant. It returns a list of restaurants with the same cuisine that have a greater review average than the original restaurant. It then saves the list to the database.

I have tried to create the query in linq but I keep on recieving errors such as Cannot implicitly convert type 'System.Collections.Generic.List<int>' to 'int' or the results of the query are wrong.

Any help would be greatful.

Error Line

var getrestaurantaverage = db.Reviews
    .Where(r => r.RestaurantId = getrestaurantsid)
    .Average(r => r.score);

Suggestion Function

var averagescore = db.Reviews
    .Where(r => r.RestaurantId == review.RestaurantId)
    .Average(r => r.score);

var getrestaurantsid = (from r in db.Reviews
                       where r.Cuisine == review.Cuisine
                       select r.RestaurantId).ToList();
var getrestaurantaverage = db.Reviews
    .Where(r => r.RestaurantId = getrestaurantsid)
    .Average(r => r.score);

var choices = (from r in db.Reviews
               where getrestaurantaverage >= averagescore
               select r.RestaurantId).ToList();
foreach (var item in choices)
{
    var suggestion = new Suggestion()
    {
        reviewid = review.id,
        Userid = review.UserId,
        restaurantid = item
    };
    db.Suggestions.Add(suggestion);
    db.SaveChanges();
}

return RedirectToAction("Index", "Review");

Upvotes: 0

Views: 126

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

.Where(r => r.RestaurantId = getrestaurantsid) tries to compare an int id with a list. If you want to find resturants whose IDs are in the list, do this:

var getrestaurantaverage = db.Reviews
    .Where(r => getrestaurantsid.Contains(r.RestaurantId))
    .Average(r => r.score);

To get the average of each individual restaurant, you could create an anonymous type

var getrestaurantaverage = db.Reviews
    .Where(r => getrestaurantsid.Contains(r.RestaurantId))
    .GroupBy(r => r.RestaurantId)
    .Select(g => new { ID = g.Key, Average = g.Average(r => r.score) });

foreach (var x in getrestaurantaverage) {
    Console.WriteLine("Restaurant = {0}, average = {1}", x.ID, x.Average);
}

This gives you the average per restaurant ID.

However there is no need to get a list of restaurant ids beforehand, do it directly

var getrestaurantaverage = db.Reviews
    .Where(r => r.Cuisine == review.Cuisine)
    .GroupBy(r => r.RestaurantId)
    .Select(g => new { ID = g.Key, Average = g.Average(r => r.score) });

Upvotes: 1

Servy
Servy

Reputation: 203816

So the problem with your code is right here:

.Where(r => r.RestaurantId = getrestaurantsid)

getrestaurantsid is a list of values not one value.

We can change this, but your problems are more significant. getrestaurantaverage cannot be just one query, with your general approach, you need to do it, *for each id in getrestaurantsid:

foreach var restaurantID in getrestaurantsid)
{
    var getrestaurantaverage = db.Reviews.Where(r => r.RestaurantId = getrestaurantsid).Average(r => r.score);

    var choices = (from r in db.Reviews
                   where getrestaurantaverage >= averagescore
                   select r.RestaurantId).ToList();
    foreach (var item in choices)
    {
        var suggestion = new Suggestion()
        {
            reviewid = review.id,
            Userid = review.UserId,
            restaurantid = item
        };
        db.Suggestions.Add(suggestion);
        db.SaveChanges();
    }

}

But this is super inefficient. You're now executing lots and lots of different DB queries. We can simplify this whole thing a lot.

var reviewAverages = db.Reviews.Where(r => r.Cuisine == review.Cuisine)
    .GroupBy(r => r.RestaurantId)
    .ToDictionary(group => group.Key, group => group.Average(r => r.score);

var myAverage = reviewAverages[review.RestaurantId];
var choices = reviewAverages.Where(pair => pair.Value >= myAverage);

And that's it. One single DB query, and only a few lines of code.

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Seems like you're making far too many DB calls to get all necessary data. You should probably try to merge all these queries into one:

from r in db.Reviews
where r.Cuisine == review.Cuisine
group r by r.RestaurantId into g
where g.Average(x => x.Score) >= db.Reviews.Where(r => r.RestaurantId == review.RestaurantId).Average(x => x.Score)
select g.Key

Upvotes: 2

Brian Driscoll
Brian Driscoll

Reputation: 19635

Your issue is here:

var getrestaurantsid = (from r in db.Reviews
                       where r.Cuisine == review.Cuisine
                       select r.RestaurantId).ToList();
var getrestaurantaverage = db.Reviews.Where(r => r.RestaurantId = getrestaurantsid).Average(r => r.score);

getrestaurantsid is of type List<int>, so the expression in the Where function below it is not going to work.

I suggest something more like this:

var getrestaurantaverage = db.Reviews.Where(r => getrestaurantsid.Contains(r.RestaurantId)).Average(r => r.score);

Upvotes: 0

Related Questions