klemsi123 klemsi123 - 1 month ago 15
C# Question

Entity Framework LINQ

How do I find which clubs have the most players in query. So the query would contain all the clubs that have the most players. For example if first club has 3 players, second club has 5 players and third club also has 5 players then there would be second and third club in the query. And i need to return list of the clubs.

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

return query.ToList();
}
}


These are classes that i use

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; }
}


I know how to do it in SQL, but not in LINQ

Thank you for help

Answer

How about dividing your query into two parts.

// 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);