如何用单条LINQ查询多对多连接表 单次数据库调用填充ViewModel

我需要借助PlayerClubs连接表，查询存在多对多（M:M）关联关系的指定club下的所有players数据。当前代码可以正常运行，但存在多次数据库调用的问题，现有实现逻辑如下：

• 第一步：从数据库中查询所有关联球员：

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

• 第二步：根据控制器传入的id查询对应的club实体：

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

• 第三步：将查询到的数据组装为ClubViewModel返回：

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

我希望仅通过一次数据库调用（即单条linq查询）完成ClubViewModel的数据填充，曾尝试编写多表join的LINQ语句实现：

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

该写法存在类型不匹配问题（Players属性为List<Player>类型，无法直接赋值单个Player实体），因此未能实现需求。

数据库表 #

PlayerClubs连接表结构：

实体类定义 #

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

不需要手动编写多表JOIN逻辑，EF Core可基于已配置的导航属性自动生成关联查询，通过投影直接填充ViewModel即可，仅会产生一次数据库调用，代码如下：

var clubVm = await _context.Clubs
    .Where(c => c.Id == id)
    .Select(c => new ClubViewModel
    {
        Club = c,
        Players = c.PlayerClubs.Select(pc => pc.Player).ToList()
    })
    .FirstOrDefaultAsync();

说明：上述写法会被EF Core翻译为单条带关联JOIN的SQL语句，一次性拉取目标俱乐部和其关联的所有球员数据，既不会产生多次数据库调用，也规避了手动JOIN时将单个Player实体直接赋值给List<Player>类型属性的类型错误。

内容的提问来源于stack exchange，提问作者Filip Gorgievski