How to retrieve object from database using LINQ expression from many-to-many join table-CodePudding

I want to retrieve all players from db for one club where they are in M:M relationship using PlayerClubs join table. My code is working but really doesn't want that approach for example, first I am retrieving all players from db

var players = await _context.PlayerClubs.Where(pc => pc.ClubId == id).Select(p => p.Player).ToListAsync();

then I retrieve the club based on id which I receive from controller

var club = await _context.Clubs.Where(z => z.Id == id).FirstOrDefaultAsync();

and lastly populate ClubViewModel with this data

return new ClubViewModel()
{
 Players = players,
 Club = club,
};

Now I want to populate this new ClubViewModel with just one db call i.e. one query using linq expression.

Things that I have tried

var query = (from c in _context.Clubs
             join pc in _context.PlayerClubs on c.Id equals pc.ClubId
             join player in _context.Players on pc.PlayerId equals player.Id
             where c.Id == id
             select new ClubViewModel
             {
                Players = player,
                Club = c,
             }).ToListAsync();

but I got stuck.

PlayerClubs table

Club

 public class Club
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
  [Required]
  public string Name { get; set; }
  [EnumDataType(typeof(Gender))]
  public Gender GenderType { get; set; }
  public int SeasonId { get; set; }
  public virtual Season Season { get; set; }
  [Required]
  public string YearOfEstablishment { get; set; }
  [Required]
  public string YearOfEntryIntoLeague { get; set; }
  public string Note { get; set; }
  [ForeignKey("League")]    
  public int LeagueId { get; set; } 
  public virtual League League { get; set; }
  public virtual ICollection<PlayerClub> PlayerClubs { get; set; }
  public virtual ICollection<CoachClub> CoachClubs { get; set; }
}

Player


public class Player
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        [Required]
        public string FullName { get; set; }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}")]
        public DateTime Birth { get; set; } 
        public int LicenseNumber { get; set; }
        public string Note { get; set; }
        public virtual List<string> Clubs { get; set; }
        public virtual List<Club> Klubovi { get; set; }
        public virtual List<string> ClubNames { get; set; }
        [StringLength(13, ErrorMessage = "Матичниот број не може да биде подолг од 13 цифри")]
        public string Embg { get; set; }        
        public virtual ICollection<PlayerClub> PlayerClubs { get; set; }

        public Player()
        {
            Clubs = new List<string>();
            ClubNames = new List<string>();
            Klubovi = new List<Club>();
        }

    }

PlayerClub

public class PlayerClub
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int Id { get; set; }
        [ForeignKey("Club")]
        public int ClubId { get; set; }
        [ForeignKey("Player")]
        public int PlayerId { get; set; }

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


    }

ClubViewModel

public class ClubViewModel : Club
    {
        public Club Club { get; set; }
        public List<Player> Players { get; set; }

        public ClubViewModel()
        {
            Players = new List<Player>();
        }
    }

CodePudding user response：

Your query:

var query = 
      from c in _context.Clubs
      where c.Id == id
      select new ClubViewModel
      {
            Players = c.PlayerClubs.Select(pc => pc.Player).ToList(),
            Club = c,
      };

var result = await query.ToListAsync();