如何使用多對多連接表中的LINQ運算式從資料庫中檢索物件

我想使用連接表players從他們有關系db的人中檢索所有內容。我的代碼正在運行，但實際上不想要這種方法，例如，首先我從clubM:MPlayerClubsplayersdb

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

然后我檢索我從中收到的club依據idcontroller

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

最后填充ClubViewModel這個data

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

現在我想ClubViewModel用一個db呼叫來填充這個新的，即one query使用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();

但我被卡住了。

PlayerClubs table

俱樂部

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

播放器

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

    }

球員俱樂部

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


    }

俱樂部視圖模型

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

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

uj5u.com熱心網友回復：

您的查詢：

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

標籤：asp.net-mvc 林克 .net-core 实体框架核心

上一篇：Linq查詢無法翻譯

下一篇：在MiniTest的Rails測驗中使用Pagyruby??gem