The LINQ expression g.Inner)' could not be translated. Either rewrite the query in a form that can be translate

Question

Help me fix or release this issue My Task: correct guesses/games played If the success rate is the same - player with fewer total tries is ranked higher Input minimum games played N - players will be included in the leaderboard if at least N games are played

My Players model:

[Table("players")]
public class Player
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Id { get; set; }

    [Required]
    [Display(Name = "Player name")]
    public string Name { get; set; }
    [JsonIgnore]
    public List<Game> Games { get; set; }
}

I am creating leaderbords list from Games table, And this is my Games model:

[Table("games")]
public class Game
{
    public Game()
    {
        this.StartDate = DateTime.Now;
        this.Attempts = 0;
        this.EndDate = null;
        this.Result = GameResultType.InGame;
    }

    [Key]
    public int ID { get; set; }

    [Required]
    [Display(Name = "Player")]
    public Player Player
    {
        get;
        set;
    }

    [Display(Name = "Count Attempts")]
    public int Attempts
    {
        get;
        set;
    }

    [Required]
    [Display(Name = "Random number")]
    [JsonIgnore]
    public int RandomNumber
    {
        get;
        set;
    }

    [Display(Name = "Game start datetime")]
    public DateTime StartDate
    {
        get;
        set;
    }

    [Display(Name = "Game end datetime")]
    public DateTime? EndDate
    {
        get;
        set;
    }

    [Display(Name = "Status of the game")]
    public StatusType Status
    {
        get;
        set;
    }

    [Display(Name = "Status of the game")]
    public GameResultType Result
    {
        get;
        set;
    }

And i should get leaderboards which players played N games

Here is my query:

var noticesGrouped = _context.Games
                .GroupBy(n => n.Player)
                .Select(group =>
                     new
                     {
                         PlayerName = group.Key.Name,
                         Games = group.Key.Games.ToList(),
                         Count = group.Key.Games.Count()
                     }).Where(x => x.Games.Count > 1).ToList();

Answer 1

You will need to materialize the objects to allow for LINQ to Object to handle your where statement instead of LINQ to SQL

You can get this working with:

var noticesGrouped = _context.Games
        .GroupBy(n => n.Player)
        .Select(group =>
             new
             {
                 PlayerName = group.Key.Name,
                 Games = group.Key.Games.ToList(),
                 Count = group.Key.Games.Count()
             })
        .AsEnumerable()
        .Where(x => x.Games.Count > 1)
        .ToList();

Read more about it here: Are Linq to SQL and Linq to Objects queries the same?

And here: The LINQ expression could not be translated and will be evaluated locally