Attempted to divide by zero Linq

Question

I'm trying to calculate average based on the sum of columns and how many there are.

Each movie can have a movierating, I'm trying to sum the movieratings together and then divide them by the amount of ratings to get the average rating for a movie.

(reviews.Where(w => w.MovieID == m.MovieID).Sum(o => o.MovieRating)) / reviews.Where(z => z.MovieID == m.MovieID).Count();

But the problem is if the count is 0. What can I do about it?

Answer 1

How about using LINQs built in Average function?

reviews
    .Where(z => z.MovieID == m.MovieID)
    .Select(z => z.MovieRating)
    .DefaultIfEmpty(0)
    .Average()

To have the query fully run on the server (depending, I guess, on your database server):

reviews
    .Where(z => z.MovieID == m.MovieID)
    .Average(z => (decimal?)z.MovieRating) ?? 0

Answer 2

You could use

var ratingList = reviews
   .Where(z => z.MovieID == m.MovieID)
   .Select(z => z.MovieRating)
   .ToList();
if(ratingList.Count > 0)
{
   double result = ratingList.Sum() / ratingList.Count;
}

Answer 3

double? score = reviews
   .Where(w => w.MovieID == m.MovieID)
   .Average(w => (double?) w.MovieRating)
   // ?? 0   // optional: pick a default when Average returns null
   ;

Answer 4

Just use Inline if condition like below:

var avr = (from a1 in reviews
           where a1.MovieID == m.MovieID
           group a1 by a1.MovieID into g
           select new
           {
               avr = g.count() == 0 ? 0 : g.sum(r => r.MovieRating) / g.count()
           }).FirstOrDefault().avr;

Answer 5

I wanted to make a single query (because perhaps we are working on a DB, and each query has a cost "just because it is a query") and do all the computation SQL-side, so that I receive back a single row (because if there are 1000 reviews, I don't really need to download all of them... I want the sum of their MovieRating and their count).

// Based on https://stackoverflow.com/a/45144198/613130
var result = reviews
    .Where(x => x.MovieID == m.MovieID)
    .GroupBy(x => 1)
    .Select(x => ((double?)x.Sum(y => y.MovieRating)) / x.Count())
    .FirstOrDefault() ?? -1.0;

The result will be -1.0 if there aren't reviews for the MovieID. Note the use of cast because otherwise we will have an integer division probably (if MovieRating is an integer).

Note that there won't be any division if a row isn't found (because there won't be any Group)

The equivalent query on a SQL database should be

SELECT CAST(SUM(x.MovieRating) AS FLOAT) / COUNT(*) 
    FROM Reviews x 
    WHERE x.MovieID = @MovieID 
    GROUP BY 1

But in truth the EF translated it in a much more complex way :-) (tested on EF 6.2.0):

SELECT 
    CAST( [GroupBy1].[A1] AS float) /  CAST( [GroupBy1].[A2] AS float) AS [C1]
FROM ( SELECT 
    [Filter1].[K1] AS [K1], 
    SUM([Filter1].[A1]) AS [A1], 
    COUNT([Filter1].[A2]) AS [A2]
    FROM ( SELECT 
        1 AS [K1], 
        [Extent1].[MovieRating] AS [A1], 
        1 AS [A2]
        FROM #Reviews AS [Extent1]
        WHERE [Extent1].[MovieId] = @p__linq__0
    )  AS [Filter1]
    GROUP BY [K1]
)  AS [GroupBy1]

Answer 6

Not certain if there's a particular emphasis on use of LINQ, but I would prefer to put it all in a for/foreach loop rather than multiple LINQ. Otherwise below would work?

var matchingReviews = reviews.Where(w => w.MovieID == m.MovieID);
int matchingReviewCount = matchingReviews.Count();
if (matchingReviewCount  > 0)
{
    matchingReviews.Sum(o => o.MovieRating) / matchingReviewCount;
}
else
{
    //return default value?
}