CODEWITHSUNDEEP
linqlinq-to-entities
Patrick Peters
Patrick Peters

Reputation: 9568

LINQ how get max rating

I have a model with the following entities:

Game: Id
User: Id
UserRating: Id, UserId, GameId, Value

Users can give a rating for a game.

I want to have a query that returns the top 5 games with the highest rating. The highest rating should be based on the Value, but when there are 2 or more games with the same rating, the count should also be taken into account.

How do express this in a query, return the result list as Game entities, using lambda expressions ?

(using EF 4, MVC, SQL Server).

Upvotes: 0

Views: 326

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500435

I assume that by "highest rating" you mean "highest average rating"? It sounds like you want something like:

var query = from rating in db.UserRating
            group rating by rating.GameId into ratings
            orderby ratings.Average(x => x.Value) descending,
                    ratings.Count() descending
            join game in db.Game on ratings.Key equals game.Id
            select game;

var top5Games = query.Take(5);

EDIT: In non-query-expression form, this would be more painful, though still feasible:

var top5Games = db.UserRating
                  .GroupBy(rating => rating.GameId)
                  .OrderByDescending(ratings => ratings.Average(x => x.Value))
                  .ThenByDescending(ratings => ratings.Count())
                  .Join(db.Game, r => r.Key, g => g.Id, (r, g) => g)
                  .Take(5);

In this case doing it in the lambda syntax isn't too bad, as you're only getting the game out of the join... but more generally, it becomes nastier. It's definitely worth understanding and using both forms, depending on which is the simpler approach for the query at hand. In particular, when you start doing multiple joins it becomes horrible in lambda syntax.

Note that this won't give the rating for that game... to get that, you'd probably want something like:

select new { Game = game,
             RatingAverage = ratings.Average(x => x.Value),
             RatingCount = ratings.Count() }

at the end of the query instead of just select game.

Also note that you may want to exclude games which currently don't have enough ratings to be meaningful yet - otherwise a game with a single rating which is "perfect" will always be at the top.

EDIT: Okay, with the final tie-break of "name of game", I'd definitely use the query expression form:

var query = from rating in db.UserRating
            join game in db.Game on ratings.Key equals game.Id
            select new { rating, game } into pair
            group pair by pair.game into pairs
            orderby pairs.Average(x => x.rating.Value) descending,
                    pairs.Count() descending,
                    pairs.Key.Name
            select pairs.Key;

var top5Games = query.Take(5);

Upvotes: 2

Related Questions