c# - Entity Framework LINQ

Question

如何找到哪些俱乐部拥有最多的查询球员。因此查询将包含拥有最多球员的所有俱乐部。例如，如果第一俱乐部有 3 名球员，第二俱乐部有 5 名球员，第三俱乐部也有 5 名球员，那么查询中将有第二和第三俱乐部。我需要返回俱乐部列表。

public List<Club> ClubWithMostPlayers()
{
     using (var context = new dataContext())
     {
          var query = ?????

          return query.ToList();
     }
}

这些是我使用的类

public class Player
{
    public int PlayerId { get; set; }
    public string name { get; set; }
    public string surname { get; set; }
    public int yearOfBirth { get; set; }

    public virtual List<PlayerClub> playerClub { get; set; }
}

public class Club
{
    public int ClubId { get; set; }
    public string name { get; set; }
    public string stadium { get; set; }
    public int yearOfConstruction { get; set; }

    public virtual List<PlayerClub> playerClub { get; set; }
}

public class PlayerClub
{
    public int PlayerClubId { get; set; }

    public int PlayerId { get; set; }
    public int ClubId { get; set; }

    public virtual Player player { get; set; }
    public virtual Club club { get; set; }

    public int from { get; set; }
    public int to { get; set; }
    public int appearances { get; set; }
}

我知道如何在 SQL 中执行此操作，但不知道如何在 LINQ 中执行此操作

感谢您的帮助

Answer 1

将查询分成两部分怎么样。

// get number of maximum number of players in one club
var count = context.Clubs.Select(c => c.playerClub.Count()).Max();

// get clubs with specific number of players
var query = context.Clubs
    .Where(c => c.playerClub.Count() == count);